Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_REFRESH
icon5.gif  DBMS_REFRESH [message #212825] Mon, 08 January 2007 08:17 Go to next message
shelby
Messages: 1
Registered: January 2007
Junior Member
Folks,

Does anybody know how to solve following problem:
I have a package included group of nested procedures. Inside one of them there's refreshing materialized view made in that way:

dbms_mview.refresh('MV_NAME', 'C', '', TRUE, FALSE, 0, 0, 0, TRUE);

Unfortunately this refresh makes automatic Commit also. I want to rollback changes when some exception occures. I know that there's something like ROLLBACK TO SAVEPOINT. When I run following code:

BEGIN
SAVEPOINT right_here;
do_my_stuff;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO right_here;
END;

I get back error: SAVEPOINT right_here is not established. Does anybody know how to change TRUNCATE on DELETE in refresh function. I think it could help meGive me some exemples if you can. Thanks for all kinds of you.

Rgds,
Chris
Re: DBMS_REFRESH [message #212834 is a reply to message #212825] Mon, 08 January 2007 08:48 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

I wonder what your "do_my_stuff" do really do...

Read THIS About rollback,commits and savepoints

It will probably give you the answer for your ORA-01086
Previous Topic: Case/Decode
Next Topic: Need help on the query
Goto Forum:
  


Current Time: Wed Dec 07 22:10:34 CST 2016

Total time taken to generate the page: 0.05790 seconds