Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE open file
UTL_FILE open file [message #324585] Tue, 03 June 2008 04:10 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Reading a File using UTL_FILE [message #41027]
I'm trying using the UTL_FILE test_file package which was posted in above forum.

I'm encountering problem as below:

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "APPS.TEST_FILE", line 14
ORA-06512: at line 1

So, I suspect is due to the file location problem, my file location is :

l_file := utl_file.fopen('/apps/myfile/upload/', 'test.dat', 'r'); The file is store in our solaries server

pls advise.

Ying
Re: UTL_FILE open file [message #324590 is a reply to message #324585] Tue, 03 June 2008 04:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Is your database running on the same server where you are running you are code. In other words, can database see the directory path you have mentioned. Also, check whether oracle process has the right privileges (i.e) read/write to the directory mentioned. If it cannot see the path then it is not possible to use utl_file. Also, by looking at your syntax my best guess will be you are using oracle 8. If that is the case have you mentioned this directory location in the utl_file_dir parameter in the init.ora. Remember if you change your init.ora you should bounce your database.

Please, always post your oracle version and follow the forum guidelines.



Regards

Raj

[Updated on: Tue, 03 June 2008 04:26]

Report message to a moderator

Re: UTL_FILE open file [message #325143 is a reply to message #324590] Thu, 05 June 2008 02:30 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Dear S.Rajaram,

Yup..sorry about that,you are correct, we are using version 8.1.7. Thanks so much for your information. Base on your informatoon I further my study by searching the forum on "utl_file_dir parameter in the init.ora" as I really don't have idea what is this. Finally I found something in "how to set utl_file_dir parameter(ini.ora) [message #124312]".

So from the "show parameter utl_file_dir" command, I managed to get parameter setting, as below:


SQL> show parameter utl_file_dir

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /usr/tmp, /apps/proddb/8.1.7/a
ppsutil/outbound/PROD, /apps/myfile/upload/

So from here can see that the directory has been defined.

Second, the directory has been given read & write access. May I know how do I check this -> "Also, check whether oracle process has the right privileges (i.e) read/write to the directory mentioned.".


pls advise....

Regards,
Ying
Re: UTL_FILE open file [message #326003 is a reply to message #324585] Mon, 09 June 2008 22:00 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Hope that can get reply on this. How do we check "whether oracle process has the right privileges (i.e) read/write to the directory mentioned"?

Since the UTL package is in SYS schema, does it mean that we need to grant the read and write privileges (i.e) read/write of the directory to SYS?

Pls advise.

Ying
Re: UTL_FILE open file [message #336119 is a reply to message #324590] Thu, 24 July 2008 22:12 Go to previous message
ying
Messages: 143
Registered: May 2002
Senior Member
Just would like to update that I have found the problem and solution. THis is because of the directory path:

_file := utl_file.fopen('/apps/myfile/upload/', 'test.dat', 'r'); The file is store in our solaries server


The correct one should be:
_file := utl_file.fopen('/apps/myfile/upload', 'test.dat', 'r'); The file is store in our solaries server
Previous Topic: Update CLOB column
Next Topic: UTL - Check is file existed
Goto Forum:
  


Current Time: Tue Feb 11 20:28:51 CST 2025