RE: PL/SQL exceptions handling help

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Thu, 29 Mar 2007 13:06:23 -0600
Message-ID: <7209E76DACFED9469D4F5169F9880C7A67572C@mail01bldr.arraybp.com>

It looks like your handler is outside the scope of the anonymous block begining at line 11.

--

Ron Reidy
Lead DBA
Array BioPharma, Inc.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Day Sent: Thursday, March 29, 2007 11:32 AM
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> This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 29 2007 - 14:06:23 CDT