I spent only a minute looking at this. So I may be wrong on this. 12 need_complete_refresh exception; 13 need_complete_refresh CONSTANT INTEGER := -12035; need_complete_refresh is first declared as an exception, then that declaration is over-written by declaring it as CONSTANT INTEGER. Try removing line# 13 to see if it helps. Regards, Arul On 3/29/07, Thomas Day <tomdaytwo@xxxxxxxxx> wrote:
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>
-- Arul