RE: PL/SQL exceptions handling help

This seems a scope problem at first look. You declare the exception
inside the begin-end block in the procedure, but your "exception when"
clause is outside the begin-end block.

 

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Day
Sent: Thursday, March 29, 2007 8:32 PM
To: oracle-l Freelists
Subject: PL/SQL exceptions handling help

 

I've spent all morning R'ing the FMs and looking up examples and I still
can't get my PL/Sql to handle my user-defined exception.  Obviously I'm
missing something that is clear as a bell to all the people who post
these examples.  Any idea what it is? 

 

Oracle 9i on AIX

 

SQL> l
  1  create or replace
  2  procedure refresh_snapshot (mv_id IN varchar2) is
  3  ERR_NUM NUMBER:=0;
  4  ERR_MSG VARCHAR2(100):=null;
  5  v_owner varchar2(100):=null;
  6  v_mview varchar2(100):=null; 
  7  sql_stmt varchar2(200):=null;
  8  v_action varchar2(1):='F';
  9  BEGIN
 10   select username into v_owner from user_users;
 11  DECLARE
 12   need_complete_refresh exception;
 13   need_complete_refresh CONSTANT INTEGER := -12035; 
 14
 15    pragma exception_init(need_complete_refresh,-12035);
 16   Cursor V_SNAPSHOTS is
 17    select name from user_snapshots;
 18   BEGIN
 19    Open V_SNAPSHOTS;
 20     LOOP
 21       FETCH V_SNAPSHOTS INTO v_mview; 
 22        EXIT WHEN V_SNAPSHOTS%NOTFOUND;
 23    DBMS_SNAPSHOT.REFRESH('||v_owner||.||v_mview||','||v_action||');
 24
sql_stmt:='DBMS_SNAPSHOT.REFRESH('||v_owner||'.'||v_mview||','||v_action
||');'; 
 25     END LOOP;
 26    CLOSE V_SNAPSHOTS;
 27     END;
 28      EXCEPTION WHEN need_complete_refresh then v_action:='C';
 29     DBMS_SNAPSHOT.REFRESH('||v_owner||.||v_mview||','||v_action||');

 30
sql_stmt:='DBMS_SNAPSHOT.REFRESH('||v_owner||'.'||v_mview||','||v_action
||');';
 31  --   EXCEPTION
 32    WHEN OTHERS THEN
 33     err_num := SQLCODE;
 34     err_msg := SUBSTR(SQLERRM, 1, 100); 
 35  DBMS_OUTPUT.PUT_LINE (sql_stmt);
 36  DBMS_OUTPUT.PUT_LINE (ERR_NUM||' '||ERR_MSG||' snapshot is
'||mv_ID);
 37*    END refresh_snapshot;
SQL> sho errors
Errors for PROCEDURE REFRESH_SNAPSHOT: 

LINE/COL ERROR
--------
-----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
12/2     PLS-00704: 'NEED_COMPLETE_REFRESH' must be declared as an 
         exception

14/3     PL/SQL: Item ignored
27/20    PLS-00201: identifier 'NEED_COMPLETE_REFRESH' must be declared
SQL>

Other related posts: