Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error handler for ORA-29283 - not working

520430Jun 27 2006 — edited Oct 11 2007
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 8 2007
Added on Jun 27 2006
4 comments
4,518 views