-
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;
/
-
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.
-
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
-
Try setting the parameter with quotes
utl_file_dir = "*" . Make sure the Oracle owner
account has write access to that directory.
- RK
-
Did you reboot the database after modifying the init.ora ?
-
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
