error handler for ORA-29283 - not working
520430Jun 27 2006 — edited Oct 11 2007I am running Oracle 9.2.0.4 on HP-UX.
I have a stored procedure which reads a text file. I have set up an execption for error code ORA-29283 (invalid file operation). When I test my procedure (by not having the file to read) my exception handler is bypassed for a general exception error and my procedure terminates.
here's parts of my code:
Declare
file_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT (file_not_found, -29283);
BEGIN
nochourly_file := UTIL_FILE.fopen('/mydirectory','myfile.txt','R');
.
Loop
begin
UTL_FILE.get_line(nochourly_file, sbuffer);
-
-
EXCEPTION
WHEN NO_DATA_FOUND
THEN
GOTO end_of_file;
-
WHEN file_not_found
THEN
DBMS_OUTPUT.put_line ('Invalid File Operation - file not found');
skip_last_hour_processed;
GOTO end_of_file;
WHEN OTHERS
THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);
DBMS_OUTPUT.put_line ('Error ' || TO_CHAR (err_num));
DBMS_OUTPUT.put_line (err_msg);
DBMS_OUTPUT.put_line (sbuffer);
RAISE;
EXIT;
END;
-
-
===============
When this fails I expect to see the message
"Invalid file operation - file not found"
which indicates my exception handler was processed.
Instead I see:
SQL> @$HOME/newhourly_dly
Begin processing at 20060627154321
nlasthourprocessed:20060627100000
Last Hour Processed is 20060627100000
BEGIN noc_hourly_daily_load; END;
*
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 "HNS.NOC_HOURLY_DAILY_LOAD", line 374
ORA-06512: at line 1
Elapsed: 00:00:00.05
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Can you explain:
1) what generated the error message since
a. it wasn't my exception handler and
b. it wasn't the "WHEN OTHERS"
2) Why isn't my error handler working?
Please advise.
Thank you