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".

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