UTL_FILE open file [message #324585] |
Tue, 03 June 2008 04:10  |
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   |
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   |
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   |
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  |
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
|
|
|