Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-29280 on Unix (UTL_FILE)

303567Nov 19 2004 — edited Nov 19 2004
I have the following entry when I query ALL_DIRECTORIES:

SQL>SELECT * FROM ALL_DIRECTORIES

OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS UTIL_DIR_INVF /prod/home01/store/dco/logs/PINVF

and I have the directory permissions on Unix as follows:

u96010@cassatt:/prod/home01/store/dco/logs->ls -lt
total 654
drwxrwxrwx 2 dcoqa dssapps 96 Nov 12 13:02 PINVF

The directory has 777 privileges (hence open to everyone)

And I have the folowing code raise ORA-29280 exception:

CREATE OR REPLACE PROCEDURE UTL_FILE_OP (
pin_message IN VARCHAR2,
pin_File_Type IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;

e_INVALID_PATH EXCEPTION;
e_INVALID_MODE EXCEPTION;
e_INVALID_FILEHANDLE EXCEPTION;
e_INVALID_OPERATION EXCEPTION;
e_WRITE_ERROR EXCEPTION;
e_INTERNAL_ERROR EXCEPTION;
e_INVALID_FILENAME EXCEPTION;
e_ACCESS_DENIED EXCEPTION;

PRAGMA EXCEPTION_INIT (e_INVALID_PATH, -29280);
PRAGMA EXCEPTION_INIT (e_INVALID_MODE, -29281);
PRAGMA EXCEPTION_INIT (e_INVALID_FILEHANDLE, -29282);
PRAGMA EXCEPTION_INIT (e_INVALID_OPERATION, -29283);
PRAGMA EXCEPTION_INIT (e_WRITE_ERROR, -29285);
PRAGMA EXCEPTION_INIT (e_INTERNAL_ERROR, -29286);
PRAGMA EXCEPTION_INIT (e_INVALID_FILENAME, -29288);
PRAGMA EXCEPTION_INIT (e_ACCESS_DENIED, -29289);

file_handle UTL_FILE.FILE_TYPE;
BEGIN
file_handle := UTL_FILE.FOPEN('/prod/home01/store/dco/logs/PINVF', 'File_Proc_Stat.txt', 'W');
UTL_FILE.PUT_LINE (file_handle, pin_message);
UTL_FILE.FCLOSE (file_handle);
EXCEPTION
WHEN e_INVALID_PATH THEN
insert_into_error_log(SQLCODE, 'INVALID_PATH', pin_File_Type || ': INVALID PATH SPECIFIED');
WHEN e_INVALID_MODE THEN
insert_into_error_log(SQLCODE, 'INVALID_MODE', pin_File_Type || ': INVALID FILE MODE FOR UTL_FILE OPS');
WHEN e_INVALID_FILEHANDLE THEN
insert_into_error_log(SQLCODE, 'INVALID_FILEHANDLE', pin_File_Type || ': INVALID FILE HANDLE');
WHEN e_INVALID_OPERATION THEN
insert_into_error_log(SQLCODE, 'INVALID_OPERATION', pin_File_Type || ': INVALID FILE OPERATION');
WHEN e_WRITE_ERROR THEN
insert_into_error_log(SQLCODE, 'WRITE_ERROR', pin_File_Type || ': ERROR WRITING TO FILE');
WHEN e_INTERNAL_ERROR THEN
insert_into_error_log(SQLCODE, 'INTERNAL_ERROR', pin_File_Type || ': INTERNAL ERROR DURING UTL_FILE OPS');
WHEN e_INVALID_FILENAME THEN
insert_into_error_log(SQLCODE, 'INVALID_FILENAME', pin_File_Type || ': FILE DOES NOT EXIST');
WHEN e_ACCESS_DENIED THEN
insert_into_error_log(SQLCODE, 'ACCESS_DENIED', pin_File_Type || ': ACCESS TO FILE/DIR DENIED');
WHEN OTHERS THEN
insert_into_error_log(SQLCODE, SQLERRM, pin_File_Type || ': UNKNOWN UTL_FILE ERR');
END UTL_FILE_OP;


Can you please guide me to resolve the problem?

Thanks
Kishore
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 17 2004
Added on Nov 19 2004
3 comments
600 views