ok another week, another fight with oracle procedures.

This time I figured out that I need some materialized views to speed up my statistics. So what you do?

You create a basic script...

bla bla bla

...create materialized view ...

bla bla bla


now you execute this script and all is fine and dandy, except...

the next time you execute this script, it crashes because of the obect exists already.

A simple fix to this problem is:

--check if the view exist and if this is the case drop it first--
open get_results for 'select count(*) from all_mviews where mview_name = UPPER(''MY_FANCY_VIEW'') and owner = UPPER('''||user||''')';
fetch get_results into maxCount;
close get_results;

DBMS_OUTPUT.PUT_LINE('count is: ' ||maxCount);
if maxCount > 0
then
DBMS_OUTPUT.PUT_LINE('dropping existing view');
execute immediate ('drop materialized view '||user||'.my_fancy_view');
else
DBMS_OUTPUT.PUT_LINE('no view exist with this name');
end if;

...create materialized view yada yada yada...


you can find the infos...

...about all registered views using:

select * from all_views

...about all registered materialized views

select * from all_mviews

...about all registered tables

select * from all_tables