Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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...
FAX: 734-930-7611 E-Mail: jim.silverman_at_thomson.com From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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 upexamples 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
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
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-lReceived on Thu Mar 29 2007 - 14:19:24 CDT
![]() |
![]() |