UTL_FILE invalid path error

[mmemon]

Hi Everyone,

I have a stored procedure which inserts data from 1 table to another on a weekly basis. If an exception is raised then I am calling the utl_file package to write out an error text file. My problem is:I keep receiving an invalid path error.

My database is on Sun Solaris.
my init.ora file has this parameter in it:
UTL_FILE_DIR = /u01/app/oracle/product/9.0.1/utl_file
**I shutdown and restarted the database.

My code has this:
LDAT_OUTPUT := utl_file.FOPEN('/u01/app/oracle/product/9.0.1/utl_file','weeklyLDAT.txt','w');

I understand the owner of the oracle processes should have access to the directory where the text file is written and the user does.
I also issued a command:
chmod 777 on the directory above so all users can have read and write privileges.

Is there something else I am missing?

Thanks
Michele

 

[carp]

Well, let's first confirm all of our assumptions:

Has the database been bounced since you made the utl_file_dir entry in your init.ora?

See what you get when you query:

SELECT value
FROM v$parameter
WHERE name = 'utl_file_dir';

 

[mmemon]

Hi,

Yes, I have bounced the server since the entry. But there is no vale for the parameter. I am using oracle 9i, Is this parameter still supported?

I am using a tool called webmin to edit the init.ora file and I am signed as root.

 

[sem]

I'd suggest you to don't do anything with Oracle from root. I suppose that you edited wrong file. You may explicitly specify it during startup or try to find one really being used.

Regards, Dima

 

[ajayarora]

Hi,

Does "others" have write permissions to the directory mentioned ?

Regards
Ajay.

 

[mmemon]

Hi,

I didn't edit the wrong file. If anything I edited it as the root user. But I am reading that Oracle 9i uses a pfile instead of initSID.ora file first.

Michele

 

[sem]

Do you mean spfile? initSID.ora IS pfile

Regards, Dima

 

[mmemon]

Hi,

Yes, I meant to type spfile. Sorry.

Well, as an alternative I am using the spool command to write out my error messages.

But I woulld like to get the utl_file to work.

Thank you
Michele

 

[jee]

Well you just need to get the parameter into your spfile (or setup to use a pfile - you can still do that in Oracle 9). To update your spfile, you first export it to a flat file, then edit it to add the UTL_FILE_DIR parameter, then import it back. Instructions are in the Oracle Administrator's Guide. With an SPFILE some paramaters can be altered on the fly with the ALTER SYSTEM command, but UTL_FILE_DIR is not one of these.

However, with 9i, Oracle recommends discontinuing usage of the UTL_FILE_DIR parameter and they provide an alternate method:

"In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default."

UTL_FILE_DIR still works (I am using it on a 9i system with an spfile) if you want to go that way.