RE: PL/SQL exceptions handling help

Tom, I noticed a couple of problems with your code.

First, you are declaring the identifier NEED_COMPLETE_REFRESH as both an
EXCEPTION and as a CONSTANT INTEGER; it can't be both.  Remove the
second declaration.

Second, you're declaring the EXCEPTION within an inner block, yet the
handler appears in the outer block.  Since the scoping rules don't allow
the outer block to "see" any local objects declared in the inner block,
the handler doesn't know about this declaration.  Therefore, you'll need
to decide whether it is appropriate to either (a) move the declaration
of the EXCEPTION into the outer block (i.e., in the procedure header,
before the first BEGIN statement), or (b) move the handler into the
inner block (i.e., that starts with the DECLARE statement).  The
semantics of the procedure will dictate which is appropriate.

Finally, as an aside, I'm a bit confused by the arguments you're passing
to dbms_snapshot.refresh.  Since each is enclosed in apostrophes, it is
these constant character values, not the expression concatenating the
values of the variables v_owner, v_mview, and v_action, that will be
passed.  Just FYI...

HTH...
===================================== 
Jim Silverman 
Senior Systems Database Administrator 
Solucient, LLC - A Thomson Company 
Telephone:   734-669-7641 
FAX:            734-930-7611 
E-Mail:         jim.silverman@xxxxxxxxxxx 
                
                From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Day
                Sent: Thursday, March 29, 2007 1: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> 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: