Commit necessary after Dbms_Refresh.Refresh?

[sjh]

Hi,

I have an Oracle procedure that has the Dbms_Refresh.Refresh statement to refresh a materialized view before processing records. It later calls the materialized view to retrieve a value. Is it necessary to add the Commit after the Dbms_Refresh.Refresh?

Thank you in advance...

 

[SantaMufasa]

SJH,

A quick way to test whether a COMMIT occurs is to [ul][li]Update a value in a table[/li][li]Open another session and query the value you just updated. (You should not be able to see the updated value since you have not done a COMMIT in the original session.)[/li][li]Execute the DBMS_REFRESH.REFRESH procedure against an MV from your original session (in which the UPDATE occurred).[/li][li]Go back to your second session and re-execute the same query.[/li][/ul] If you see the new value, then ...REFRESH committed; if you cannot seet the new value, then COMMIT did not occur yet. (This technique applies the priciple that "One test is worth 100 expert opinions." <grin>)


Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[sjh]

I followed your instructions, and the DBMS_REFRESH.REFRESH procedure did not COMMIT.

Thank you!