Error running procedure using SYS.UTIL_FILE
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
Re: Error running procedure using SYS.UTIL_FILE
Quote:
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?