Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL exceptions handling help

RE: PL/SQL exceptions handling help

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 29 Mar 2007 20:41:29 +0100
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663C068@ENYC11P32005.corpny.csfb.com>


The problem is the declaration of  

need_complete_refresh CONSTANT INTEGER := -12035;

Remove that and it should compile.      

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.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 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>

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US