Re: PL/SQL exceptions handling help

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 ('DBMS_SNAPSHOT.REFRESH('||sql_stmt||');');

dbms_snapshot.refresh (list=> v_owner || '.' || v_mview, method=>'f'); else null;

end if;

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 ('DBMS_SNAPSHOT.REFRESH('||sql_stmt||');');

dbms_snapshot.refresh (list=> v_owner || '.' || v_mview, method=>'c'); 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;
/

Again, thanks to everyone for their comments. Rjamya suggested a different form of the call to DBMS_SNAPSHOT.REFRESH and that overcame the problem.

The procedure is now completely generic. By compiling it in a schema and then granting execute on the procedure to another schema I can have the second schema run the snapshots without having to give the ALTER ANY SNAPSHOT privilege.

The listing below is the output from DBMS_OUTPUT.PUT_LINE, showing me that it is looping through the entire list of snapshots.

SQL> exec refresh_snapshot

DBMS_SNAPSHOT.REFRESH('DSSSTD.HAIR_COLOR_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.GOVT_AGENCY_TYPE_CD_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.SMUGLE_CLAIM_STATUS_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.SMUGLE_METHOD_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.OPERTN_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.ADMSN_CLASS_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.AIR_CARR_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.EVENT_G23_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.GENDER_TYPE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.AGENCY_TYPE_CD_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.TRAVEL_MODE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.AGENCY_CD_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.RGSTRTN_CRIT_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.PROGRM_AREA_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.ETHNIC_TYPE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.TM_ILEGAL_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.PROCESG_DSPOSTN_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.YES_NO_IND_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.CNVYNC_CD_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.ENTRY_STATUS_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.RGSTRTN_RFRL_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.ENTRY_STATUS_FOUND_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.ADR_TYPE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.EYE_COLOR_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.RACE_TYPE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.BORDER_TYPE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.ARREST_METHOD_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.ENCNTR_ROLE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.STATE_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.CNTRY_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.MARITL_STATUS_REF','F');
DBMS_SNAPSHOT.REFRESH('DSSSTD.CMPLXN_TYPE_REF','F');

PL/SQL procedure successfully completed.

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