Materialized view dynamic row count

Hi all,

Apologies if not the correct c.d.o.xxx group ...

Oracle 9.2.x.x on RHAS3 ... data warehouse implementation ...

We have a number (currently a couple of dozen but growing fast) of materialized views - refresh on demand - which are to be repopulated overnight. Results of the refresh to be written to a logging table.

Proposed process is to have a simple table with columns VIEW_NAME and REFRESH_ORDER and a stored proc fired by cron containing (simplified code below):

procedure LOAD_MVIEWS(v_cron_job_no in number) is cursor current_mviews is

    select VIEW_NAME
    from MVIEWS_TABLE
    order by REFRESH_ORDER;
v_pre_count number;
v_post_count number;
begin

    for c in current_mviews loop

• help setting v_pre_count needed here -- dbms_mview.refresh(c.VIEW_NAME, 'C');
• and help setting v_post_count needed here -- insert into CRON_LOG_TABLE(job_no, tab_name, pre_rows, post_rows) values (v_cron_job_no, c.VIEW_NAME, v_pre_count, v_post_count); end loop; end LOAD_MVIEWS;

Advice sought on the most efficient way of getting row counts on the dynamically named materialized views within the cursor loop.

TIA Geoff M Received on Mon Oct 10 2005 - 19:56:43 CDT