-
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
-
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;
-
Re: Error running procedure using SYS.UTIL_FILE
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?
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|