Re: PL/SQL exceptions handling help
- From: "Thomas Day" <tomdaytwo@xxxxxxxxx>
- To: "oracle-l Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Apr 2007 13:15:55 -0400
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.
Other related posts: