Re: PL/SQL exceptions handling help
- From: "Arul Ramachandran" <contactarul@xxxxxxxxx>
- To: tomdaytwo@xxxxxxxxx
- Date: Fri, 30 Mar 2007 08:04:47 -0700
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
Other related posts:
