Oracle 9i, utl_file_dir and CREATE DIRECTORY - problematic

[adcworks]

Hello,

I need to write a log file to my linux filsystem. I had this going on Oracle 8i but Oracle 9i is proving problematic...I have done the following...

1) Tried the new way

CREATE DIRECTORY utl_file_logs AS '/path/to/logdir'#
GRANT READ ON DIRECTORY TO user;
GRANT WRITE ON DIRECTORY TO user;

Then;

PROCEDURE debug2(p_Debug VARCHAR2) IS

v_File UTL_FILE.FILE_TYPE;

BEGIN

v_File := UTL_FILE.FOPEN('utl_file_logs', 'test.txt', 'A', 2000);
UTL_FILE.PUT_LINE(v_File, p_Debug);
UTL_FILE.FCLOSE(v_File);

END debug2;

Giving...

SQL> exec pkgname.debug2('test');
BEGIN pkgname.debug2('test'); END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "SCHEMANAME.PKGNAME", line 12
ORA-06512: at line 1

2) Tried the old way

ALTER SYSTEM SET utl_file_dir = '/path/to/logdir' SCOPE=SPFILE

rebooted instance for luck.

Using a slightly difference debug procedure gives me;

SQL> exec ugoLogging.debug('test');
BEGIN pkgname.debug('test'); END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 439
ORA-29283: invalid file operation
ORA-06512: at "SCHEMENAME.PKGNAME", line 37
ORA-06512: at line 1

==================
SO getting somewhere with the second. So I figured permissions...

did a CHMOD 777 /path/to/logdir

STILL no luck


Help!?

[kgb]

Look for error in your ugoLogging.debug('test') function.

Originally posted by adcworks

1)
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "SCHEMANAME.PKGNAME", line 12
ORA-06512: at line 1

2) Tried the old way

ALTER SYSTEM SET utl_file_dir = '/path/to/logdir' SCOPE=SPFILE

rebooted instance for luck.

Using a slightly difference debug procedure gives me;

SQL> exec ugoLogging.debug('test');
BEGIN pkgname.debug('test'); END;

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 439
ORA-29283: invalid file operation
ORA-06512: at "SCHEMENAME.PKGNAME", line 37
ORA-06512: at line 1

==================

[davey23uk]

alter system SET utl_file_dir will be lost when you reboot your instance, if you want it to remain when you bounce our instance, will need to put it in init.ora

also you cant to alter system set utl_file_dir .... in 8i

[jmodic]

Originally posted by davey23uk
[B]alter system SET utl_file_dir will be lost when you reboot your instance...

No, it won't, since he used "... SCOPE=SPFILE" in his ALTER SYSTEM command.

[saurabhg29]

alter system set utl_file_dir =* scope=spfile;

reboot the instance;
and try to run the procedure with full path to the log file.

CREATE DIRECTORY utl_file_logs AS '/path/to/logdir'#

you can't have your directory accesed.oracle docs say's
The file location must be an accessible directory, as defined in the instance's initialization parameter UTL_FILE_DIR.