RE: PL/SQL exceptions handling help
- From: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
- To: <tomdaytwo@xxxxxxxxx>, "oracle-l Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2007 15:21:21 +0300
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:
