Home » SQL & PL/SQL » SQL & PL/SQL » Handling an Error Occurred in Declaration Block of PL/SQL (Oracle Release 10.2.0.1.0, Windows XP (OS))
Handling an Error Occurred in Declaration Block of PL/SQL [message #344044] Thu, 28 August 2008 12:27 Go to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hello All,

I have a PL/SQL which has a cursor declaration as follows:

CREATE OR REPLACE PROCEDURE load_application_data
AS
       v_application_type_id INTEGER ;
       .....
       .....
       CURSOR cur_new_rec
           is  (SELECT * FROM Schema1.source_table
                MINUS
                SELECT * FROM target_table); 
BEGIN
       OPEN cur_new_rec;
       .....
EXCEPTION
       --ALL Needed exception are handled.
END;
/


Anyways the above PL/SQL program works fine. My problem is the source table in Schema1 is controlled by a different department which drops and creates the table with the new data daily. [As of why they do this, I have no clue..] Recently due to some application error, the Schema1.source_table was dropped and never recreated. So the above procedure gave up the following error:

ORA-00942: table or view does not exist


Since then, we were asked to capture such kind of exceptions and log it in error_log_tables we maintain. So I tried to capture the exception by modifying my code as follows:

CREATE OR REPLACE PROCEDURE load_application_data
AS
       v_application_type_id INTEGER ;
       .....
       .....
       TABLE_MISSING_EXCEPTION EXCEPTION; 
       PRAGMA EXCEPTION_INIT(TABLE_MISSING_EXCEPTION, -942);
       CURSOR cur_new_rec
           is  (SELECT * FROM Schema1.source_table
                MINUS
                SELECT * FROM target_table); 
BEGIN
       OPEN cur_new_rec;
       .....
EXCEPTION
       WHEN TABLE_MISSING_EXCEPTION THEN
                 Insert into ERROR_LOG_TABLE values ('ORA-00942', sysdate);
       -- Rest of the exception block.
END;
/


I understand from the Oracle documents that:

Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.

But I got the same error even after declaring the cursor in an inner block.

Can anyone please guide me as to what I might have mis-coded here?

Thanks in advance,
Joice
Re: Handling an Error Occurred in Declaration Block of PL/SQL [message #344057 is a reply to message #344044] Thu, 28 August 2008 12:49 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I believe your problem is that the error is being encountered when the system tries to re-compile the procedure which has become invalidated as a result of the dropped table. No amount of error handling in the procedure is going to be able to catch that as the error is happening before the procedure can be entered at all.
Re: Handling an Error Occurred in Declaration Block of PL/SQL [message #344060 is a reply to message #344044] Thu, 28 August 2008 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As the cursor is static it is checked at compile time and not during execution, so you can't trap it.
You have to use dynamic SQL and ref cursor.

Regards
Michel
Re: Handling an Error Occurred in Declaration Block of PL/SQL [message #344080 is a reply to message #344060] Thu, 28 August 2008 13:31 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Annagel,
Quote:

I believe your problem is that the error is being encountered when the system tries to re-compile the procedure which has become invalidated as a result of the dropped table. No amount of error handling in the procedure is going to be able to catch that as the error is happening before the procedure can be entered at all.


Thanks annagel for your advice.

Michel,
Quote:
You have to use dynamic SQL and ref cursor.


Thanks for your guidance. I am kind of new to these concepts. I will see if I succeed on my issue with these concepts. Thanks again...
Previous Topic: missing right paranthesis
Next Topic: null problem
Goto Forum:
  


Current Time: Thu Dec 08 12:28:26 CST 2016

Total time taken to generate the page: 0.11767 seconds