plz. help me it is very urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: plz. help me it is very urgent

  1. #1
    Join Date
    Sep 2000
    Posts
    305
    I am getting problem in File I/O
    plz. help me
    I am not able to open a file through procedure
    it is giving user -Defined Exception as well as it is not able to open a file I am pasting the proceudre
    as well as my files are lying in d:\loadstudent folder
    I aslo add the parameter in the init.ora
    utl_file_dir = *

    /**************************************/

    create or replace procedure loadstudent(
    P_FileDir in varchar2,
    P_FileName in varchar2,
    P_TotalInserted in out number)As
    V_FileHandle UTL_FILE.FILE_TYPE;
    V_NewLine Varchar2(100);
    V_FirstName Varchar2(100);
    V_LastName Varchar2(100);
    V_Major Varchar2(100);
    V_FirstComma number;
    V_SecondComma number;
    Begin
    V_FileHandle := UTL_FILE.FOPEN(P_FileDir,P_FileName,'r');
    P_TotalInserted:=0;
    Loop
    Begin
    UTL_FILE.GET_LINE(V_FileHandle,V_NewLine);
    Exception
    When No_Data_Found Then
    Exit;
    End;
    V_FirstComma := INSTR(V_NewLine,',',1,1);
    V_SecondComma := INSTR(V_NewLine,',',1,2);
    V_FirstName := Substr(V_NewLine,1,V_FirstComma - 1);
    V_LastName := Substr(V_NewLine,V_FirstComma + 1,V_SecondComma - V_FirstComma - 1);
    V_Major := Substr(V_NewLine,V_SecondComma + 1);
    Insert into student(ID,First_Name,Last_Name,major)
    Values(1,V_FirstName,V_LastName,V_Major);
    P_TotalInserted:=P_TotalInserted + 1;
    End Loop;
    UTL_FILE.FCLOSE(V_FileHandle);
    COMMIT;
    EXCEPTION
    When UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(V_FileHandle);
    RAISE_APPLICATION_ERROR(-20052,'Loadstudent: Invalid File Operation');
    When UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(V_FileHandle);
    RAISE_APPLICATION_ERROR(-20053,'Loadstudent: Invalid File Handle');
    When UTL_FILE.READ_ERROR THEN
    UTL_FILE.FCLOSE(V_FileHandle);
    RAISE_APPLICATION_ERROR(-20054,'Loadstudent: Read Error');
    When Others then
    UTL_FILE.FCLOSE(V_FileHandle);
    Dbms_output.put_line(sqlerrm);
    RAISE;
    END;
    /

  2. #2
    Join Date
    Sep 2000
    Posts
    77
    Shailendra,
    I think you are not having proper operating system privileges
    for reading and writting to your directory. Make sure that oracle has those privileges.
    Thanks

  3. #3
    Join Date
    Mar 2001
    Posts
    17

    Smile

    Shailendra,
    You must be missing following.......
    Accessible directories must be specified in the instance parameter initialization file (INIT.ORA).
    Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter

  4. #4
    Join Date
    Mar 2001
    Posts
    1

    Arrow

    Try setting the parameter with quotes
    utl_file_dir = "*" . Make sure the Oracle owner
    account has write access to that directory.

    - RK

  5. #5
    Join Date
    Mar 2001
    Location
    Cologne, Germany
    Posts
    24
    Did you reboot the database after modifying the init.ora ?

  6. #6
    Join Date
    Dec 2000
    Posts
    28
    First of all, keep in mind that you can only read/write files using UTL_FILE package in Server-side ( Unix, NT server etc ). We are not clear if you are trying in ( drive d:\loadstudent) is a PC or NT server.

    In local drive of ur desktop you cannot use UTL_FILE.

    Thanks,
    AD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width