| Output error message to file [message #8405] |
Mon, 18 August 2003 08:25  |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
Hi y'all
I have a procedure that copies a table from one database to another. If an error occurs during the transfer, I need to write the error into a file.
My code thus far:
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
FileHandler := UTL_FILE.FOPEN('temp','oracle_error.log', 'W');
-- *****************Process backup table data****************************************
blah blah blah
EXCEPTION
WHEN OTHERS THEN
-- some kind of error,
-- output error to log file
UTL_FILE.PUTF(fileHandler, 'Error: '||sqlcode||' '||sqlerrm||'n');
UTL_FILE.FCLOSE(fileHandler);
End of code.
Frstly, is this likely to work? Secondly the error I get at the moment is :
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 901
ORA-06512: at "PPS_OWNER.COPY_WORKSITE", line 164
ORA-29280: invalid directory path
ORA-06512: at line 1
(Line 164 is the UTL_FILE.PUTF line)
Any ideas please, I'm stuck big time with this one.
TIA
Dave
|
|
|
|
| Re: Output error message to file [message #8407 is a reply to message #8405] |
Mon, 18 August 2003 16:19  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your code works in Oracle 8i with the utl_file_dir parameter set to /temp in the init.ora file. However, since the ORA-29282 error message that you are receiving only exists in 9i, not 8i, you must be using Oracle 9i. In 9i they changed things, so that you need to create a directory object:
CREATE OR REPLACE DIRECTORY my_directory AS 'tmp';
Then you reference the directory object in upper case within your code:
FileHandler := UTL_FILE.FOPEN('MY_DIRECTORY','oracle_error.log', 'W');
Also, if you are going to use UTL_FILE.FOPEN between BEGIN and EXCEPTION, then you should include an additional UTL_FILE.FLOCSE within that same section. If the only time that you are writing to the file is within the exception block, then I would just put the UTL_FILE.FOPEN within the exception block.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
