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