Re: PL/SQL exceptions handling help

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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l