Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred (Oracle 10g,Version 10.2.0.1.0.)
UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred [message #313166] Thu, 10 April 2008 23:58 Go to next message
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 #313171 is a reply to message #313166] Fri, 11 April 2008 00:08 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have you created directory object pointing to that directory in oracle.

Re: UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred [message #313174 is a reply to message #313166] Fri, 11 April 2008 00:18 Go to previous messageGo to next message
sirige
Messages: 6
Registered: April 2008
Junior Member
Whle trying to create an object directory from Oracle It is giving Error as "Insufficient Previleges".

So I stored the text file in the folder which is already there in D: Directory.
Re: UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred [message #313175 is a reply to message #313166] Fri, 11 April 2008 00:22 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
So create a DIRECTORY object or tell your DBA to do so and get the READ/WRITE privileges on that directory.And don't start a new thread continue in your last thread.


regards,
Re: UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred [message #313176 is a reply to message #313166] Fri, 11 April 2008 00:22 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
How do one answer the unanswerable????????????
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 Go to previous messageGo to next message
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>;

Re: UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred [message #313180 is a reply to message #313166] Fri, 11 April 2008 00:28 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you use SQL*Loader or external table?

Regards
Michel
Previous Topic: Create Inline Function
Next Topic: Simulating If Then Else in Decode
Goto Forum:
  


Current Time: Wed Dec 07 02:45:26 CST 2016

Total time taken to generate the page: 0.05998 seconds