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: