Error running procedure using SYS.UTIL_FILE

[pcfltd]

Hi guys,

I have a stored procedure which copies a table from a remote database to a local one. If an error occurs during the copy it's supposed to write the error to a log file. However, it errors when writing to the error file.

9i on W2K SP 2

code follows:

procedure COPY_WORKSITE
IS

fileHandler UTL_FILE.FILE_TYPE;
v_from_table_name VARCHAR2( 30) := 'PWKS_WORKSITE';
v_to_table_name VARCHAR2( 30) := 'QWPP_PPS_WORKSITE';
v_backup_table_name VARCHAR2( 30) := 'BWPP_PPS_BACKUP';
v_dblink VARCHAR2(30) := 'get_pwks';

BEGIN


snip

EXCEPTION
WHEN OTHERS THEN
-- some kind of error,
-- output error to log file
fileHandler := UTL_FILE.FOPEN('LOG_FOLDER','oracle_error.log', 'W');
UTL_FILE.PUTF(fileHandler, TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||'Error: '||sqlcode||' '||sqlerrm||'\n');
UTL_FILE.FCLOSE(fileHandler);

snip

If the dblink is dropped to force an error the following errors occur:
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "PPS_OWNER.COPY_WORKSITE", line 166
ORA-02019: connection description for remote database not found
ORA-06512: at line 1

line 166 is the UTL_FILE.PUTF in my code above.
Clearly the last error I would expect but not the previous ones.

FYI the directory object LOG_FOLDER exists and points to a share on the server

Any help in fixing this would be very much appreciated.

Thanks
Dave

[Veen]

Make sure the Oracle User has privileges on the LOG_FOLDER -> Grant READ and WRITE access to LOG_FOLDER directory.

2) Define an User defined exceptions to handle the scenario when the remote db link is not available or db is not up.
Open file_handler to write the log file in this exception .

When using UTL_FILE package, Handle all the in-built exceptions provided ..

when utl_file.invalid_path then
Log error;
utl_file.fclose_all;
when utl_file.invalid_mode then
utl_file.fclose_all;
Log error;
when utl_file.invalid_filehandle then
Log error;
utl_file.fclose_all;
when utl_file.invalid_operation then
Log error;
utl_file.fclose_all;
when utl_file.read_error then
Log error;
utl_file.fclose_all;
when utl_file.write_error then
Log error;
utl_file.fclose_all;
when utl_file.internal_error then
Log error;
utl_file.fclose_all;
when others then
Log error;
end;

[DaPi]

Originally posted by pcfltd
9i on W2K SP 2

FYI the directory object LOG_FOLDER exists and points to a share on the server

Under NT the Oracle services run by default under the NT user SYSTEM - SYSTEM can not use network devices. Is this still a problem with Win2K?

[pcfltd]

The directory has full control for everyone so I'm fairly sure that's not the problem.

DaPi - Sorry, didn't make that clear enuff. The share the directory object points to is a directory on the Oracle machine. So it should be a local directory for the database to write to.

Thanks for the input guys, but I think I've already covered those bases. Any more ideas ?

Dave