UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred [message #313166] |
Thu, 10 April 2008 23:58  |
sirige
Messages: 6 Registered: April 2008
|
Junior Member |
|
|
Hi, I am Extracting data from a text file. The text file data is as follows:
101,George,085542242076,10000,SA_MAN
102,Henry,087742076,20000,VP
103,Grany,9849865785,1000,SA_REP
104,Mally,975642312,8599,PRESIDENT
The Procedure which I wrote in order to read data from the Text file is as follows:
CREATE OR REPLACE PROCEDURE read_emp_raw_data AS
file_handle utl_file.file_type;
file_record VARCHAR2(1000);
v_location VARCHAR2(20)/*:='D:\Sailaja'*/;
v_file_name VARCHAR2(20) /*:= 'Emp_data.txt'*/;
BEGIN
v_location := 'D:\Sailaja';
v_file_name := 'Emp_data.txt';
file_handle := utl_file.fopen(v_location,v_file_name,'R',32767);
LOOP
BEGIN
utl_file.get_line(file_handle,file_record);
INSERT INTO emp_raw_data(sno,rawdata) VALUES(seq_emp_sno.nextval,file_record);
EXCEPTION
WHEN utl_file.invalid_path THEN
dbms_output.put_line('Invalid Path Mentioned');
utl_file.fclose(file_handle);
WHEN utl_file.invalid_mode THEN
dbms_output.put_line('Invalid Mode Mentioned in the Fopen Function');
utl_file.fclose(file_handle);
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('Invalid file Handling');
utl_file.fclose(file_handle);
WHEN utl_file.invalid_operation THEN
dbms_output.put_line('Invalid Operation');
utl_file.fclose(file_handle);
WHEN utl_file.read_error THEN
dbms_output.put_line('Exception Occured while Reading the Text File');
utl_file.fclose(file_handle);
WHEN utl_file.internal_error THEN
dbms_output.put_line('Inter Error Occured');
utl_file.fclose(file_handle);
WHEN value_error THEN
dbms_output.put_line('Error Occured while Reading the Data which is too large');
utl_file.fclose(file_handle);
WHEN no_data_found THEN
dbms_output.put_line('End Of File Occured');
utl_file.fclose(file_handle);
WHEN OTHERS THEN
dbms_output.put_line('Other Exception Occured');
utl_file.fclose(file_handle);
END;
END LOOP;
utl_file.fclose(file_handle);
END read_emp_raw_data;
While Compiling Procedure is compiled Successfully. But while Running the procedure the Errors
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE",line 33
are coming. Please resolve this and let me know the solution.
|
|
|
|
|
|
|
Re: UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred [message #313178 is a reply to message #313174] |
Fri, 11 April 2008 00:27   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Query the following dictionary views to get information on directories in the database and to know which directories are accessible to you.
ALL_DIRECTORIES - all directories accessible to the database user
ALL_TAB_PRIVS - for verification whether you have the required READ/WRITE privilege on the DIRECTORY object.
If rights are not available then ask your DBA to provide those rights.
One thing more UTL_FILE writes server side files.It means you have to create your directory on server not on client and have to get access to that directory.
GRANT READ,WRITE ON DIRECTORY <directoryname> TO <username>;
|
|
|
|