Home » SQL & PL/SQL » SQL & PL/SQL » UTI_FILE: Error
UTI_FILE: Error [message #342890] Mon, 25 August 2008 07:58 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi All,

By using the UTI_FILE i am trying to open one file and reading it's contents but getting the error.The Follwoing is the code i tried.

SQL> ed
Wrote file afiedt.buf

1 create or replace procedure LOAD_DATA
2 IS
3 emp_det_rec RAVI_TEST1%rowtype;
4 file_handle UTL_FILE.FILE_TYPE;
5 data_line Varchar2(1023);
6 begin
7 file_handle:=UTL_FILE.FOPEN('D:\Personal\JAVA_DOC','object.txt','R');
8 LOOP
9 begin
10 UTL_FILE.GET_LINE(file_handle,data_line);
11 data_line:=rtrim(ltrim(data_line));
12 dbms_output.put_line(data_line);
13 EXCEPTION
14 WHEN NO_DATA_FOUND then EXIT;
15 end;
16 END LOOP;
17* END;
SQL> /

Procedure created.

SQL> exec LOAD_DATA;
BEGIN LOAD_DATA; END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "SYSTEM.LOAD_DATA", line 7
ORA-06512: at line 1

In the init.ora file i assigned

UTL_FILE_DIR = *

Pls let me know how can i overcome this.
Re: UTI_FILE: Error [message #342894 is a reply to message #342890] Mon, 25 August 2008 07:59 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
You should be using CREATE DIRECTORY command and using that object in the UTL_FILE, not a hardcoded directory name.

[added]

Oh, and I left out that UTL_FILE reads files from the server, not the client.

[Updated on: Mon, 25 August 2008 08:00]

Report message to a moderator

Re: UTI_FILE: Error [message #342899 is a reply to message #342894] Mon, 25 August 2008 08:10 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Joy,

I done as u said

SQL> create or replace directory dir_temp as 'D:\Personal\JAVA_DOC';

change in code is

file_handle:=UTL_FILE.FOPEN(dir_temp,'object.txt','R');

SQL> show error
Errors for PROCEDURE LOAD_DATA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1 PL/SQL: Statement ignored
7/29 PLS-00201: identifier 'DIR_TEMP' must be declared
Re: UTI_FILE: Error [message #342902 is a reply to message #342899] Mon, 25 August 2008 08:14 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Believe it or not (very confusing), the directory object must be in capital letter, in single quotes.
Makes it look like a literal string, but that's the syntax.
Re: UTI_FILE: Error [message #342905 is a reply to message #342902] Mon, 25 August 2008 08:19 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

No joy still the problem is persisting.

file_handle:=UTL_FILE.FOPEN('dir_temp','object.txt','R');

SQL> exec LOAD_DATA;
BEGIN LOAD_DATA; END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "SYSTEM.LOAD_DATA", line 7
ORA-06512: at line 1
Re: UTI_FILE: Error [message #342906 is a reply to message #342902] Mon, 25 August 2008 08:21 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
joy_division wrote on Mon, 25 August 2008 09:14
Believe it or not (very confusing), the directory object must be in capital letter, in single quotes.
Makes it look like a literal string, but that's the syntax.

Re: UTI_FILE: Error [message #342940 is a reply to message #342890] Mon, 25 August 2008 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe it is useless but I will repeat what I already said you:
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: UTI_FILE: Error [message #343020 is a reply to message #342890] Mon, 25 August 2008 22:56 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Thank You Very much joy,

it's working now Smile
Re: UTI_FILE: Error [message #343066 is a reply to message #342890] Tue, 26 August 2008 03:10 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
UTL_FILE_DIR = *

Last but not least please please please remove this from init.ora. You can virtually overwrite any file in the operating system which oracle user/group has access to it. Infact it is one of the reason why oracle choose to change to directory objects. So please remove that parameter or if you still insist on using it give the directories you are intersted.

Regards

Raj

[Edit:] Typo

[Updated on: Tue, 26 August 2008 03:11]

Report message to a moderator

Previous Topic: why cant we insert data, or alter dual table?
Next Topic: oracle error (merged)
Goto Forum:
  


Current Time: Wed Dec 07 05:09:55 CST 2016

Total time taken to generate the page: 0.20784 seconds