DBMS_MVIEW.explain_mview with no MV_CAPABILITIES_TABLE

Need to run DBMS_MVIEW.explain_mview in APEX SQL Workshop, but don’t have the MV_CAPABILITIES_TABLE? You’ll get this error:

ORA-30377: table ORDS_PUBLIC_USER.MV_CAPABILITIES_TABLE not found

You don’t need to create this table. You could create this table by running admin/utlxmv.sql (if you have it). Instead, you can get the output in an array and do whatever you want with its contents, e.g.:

declare
    a sys.ExplainMVArrayType;
begin
    dbms_mview.explain_mview('MY_MV',a);
    dbms_output.put_line('Explain MV '
        || a(1).mvowner || '.' || a(1).mvname);
    for i in 1..a.count loop
        dbms_output.put_line(
            rpad(a(i).capability_name, 30)
            || ' [' || case a(i).possible
                       when 'T' then 'TRUE'
                       when 'F' then 'FALSE'
                       else a(i).possible
                       end || ']'
            || case when a(i).related_num != 0 then
                   ' ' || a(i).related_text
                   || ' (' || a(i).related_num || ')'
               end
            || case when a(i).msgno != 0 then
                   ' ' || a(i).msgtxt
                   || ' (' || a(i).msgno || ')'
               end
        );
    end loop;
end;

The result will be something like this:

Now, the challenge is merely how to resolve some of those “FALSEs” …

ReportMap Release 1.4

Leave a Reply

Your email address will not be published / Required fields are marked *