ORA-29280 on Unix (UTL_FILE)
303567Nov 19 2004 — edited Nov 19 2004I 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