Re: PL/SQL exceptions handling help
- From: "Thomas Day" <tomdaytwo@xxxxxxxxx>
- To: "oracle-l Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2007 15:15:16 -0400
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:
