Re: PL/SQL exceptions handling help

Thanks to all.  Your comments were right on the money.

The proc now looks like -


create or replace

procedure refresh_snapshot is

ERR_NUM NUMBER:=0;

ERR_MSG VARCHAR2(100):=null;

v_owner varchar2(100):=null;

v_mview varchar2(100):=null;

sql_stmt varchar2(200):=null;

v_action varchar2(1):='F';

BEGIN

select username into v_owner from user_users;

DECLARE

need_complete_refresh exception;

pragma exception_init(need_complete_refresh,-12035);

Cursor V_SNAPSHOTS is

select name from user_snapshots;

BEGIN

Open V_SNAPSHOTS;

LOOP

FETCH V_SNAPSHOTS INTO v_mview;

EXIT WHEN V_SNAPSHOTS%NOTFOUND;

IF v_mview is not null then

sql_stmt:=''''||v_owner||'.'||v_mview||''''||','||''''||v_action||'''';

DBMS_OUTPUT.PUT_LINE (sql_stmt);

DBMS_OUTPUT.PUT_LINE ('DBMS_SNAPSHOT.REFRESH('||sql_stmt||');');

DBMS_SNAPSHOT.REFRESH(sql_stmt);

else null;

end if;

DBMS_OUTPUT.PUT_LINE (sql_stmt);

END LOOP;

CLOSE V_SNAPSHOTS;

EXCEPTION WHEN need_complete_refresh then v_action:='C';

sql_stmt:=''''||v_owner||'.'||v_mview||''''||','||''''||v_action||'''';

DBMS_OUTPUT.PUT_LINE (sql_stmt);

DBMS_SNAPSHOT.REFRESH(sql_stmt);

WHEN OTHERS THEN

err_num := SQLCODE;

err_msg := SUBSTR(SQLERRM, 1, 100);

DBMS_OUTPUT.PUT_LINE (ERR_NUM||' '||ERR_MSG||'--- snapshot is '||v_mview);

END;

END refresh_snapshot;

/





However, it's not a finished product.



The output looks like -



SQL> exec dsseiddata.refresh_snapshot;
'DSSEIDDATA.ARRESTS','F'
DBMS_SNAPSHOT.REFRESH('DSSEIDDATA.ARRESTS','F');
-931 ORA-00931: missing identifier--- snapshot is ARRESTS

The owner, snapshot, and all their commas are concatenating nicely into the
sql_stmt and I can output a line that looks like a valid refresh.  But I
can't get any visability into what parameters the refresh is actually trying
to use.



Very frustrating.



If it wasn't hard they wouldn't call it "work".

Other related posts: