After SQL - Refresh View Failing

[vgupta88]

I searched a lot on this forum but no luck for my solution.

I am calling below script in my AFTER Sql to refresh my Materialized view but it is failing. I already tried below options:

1) EXEC DBMS_MVIEW.REFRESH('ABC', atomic_refresh => false,out_of_place => true);

Error: ORA-00900: invalid SQL statement

2) CALL DBMS_MVIEW.REFRESH('ABC', atomic_refresh => false,out_of_place => true);

Error: ORA-06576: not a valid function or procedure name

3) BEGIN
EXEC DBMS_MVIEW.REFRESH('ABC', atomic_refresh => false,out_of_place => true);
END;
/

Error: ORA-06550: line 4, column 74:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

Can anyone help me with this.

Thanks!

[chulett]

I would check with your DBA to make sure your executing user has the correct grants to call that package. And that the syntax is correct, I believe you need to supply a "METHOD" and you may want to use TRUE rather than true, depending on settings in the database.

AFAIK, you can't call an anonymous block there. You could, however, do so in a Stored Procedure stage rather than in the After SQL area.

[qt_ky]

What I had found with Oracle Connector stage was that you have to use the CALL command in the before or after SQL. The "EXEC" or "EXECUTE" commands only work from the Oracle SQL*Plus utility. So, use your option 2 and pay attention to the error it reported. Like Craig suggested, perhaps your database ID doesn't have the privileges it needs yet.

[vgupta88]

Hi Craig and qt_ky,

Thanks for your inputs.

I can execute those commands through Toad. And I confirmed with my DBA too, I have grants too.

Not sure, what is causing the error. To keep my work going, I have created a Unix script to refresh my view which I am calling in my After Job sub-routine.

Thanks!
Vaibhav

[jerome_rajan]

Hi Vaibhav,
You need to quite simply call the unnamed block in the after-sql in the below format -

Code: Select all

BEGIN
DBMS_MVIEW.REFRESH('ABC',atomic_refresh => false, out_of_place => true);
END;

I tested it and it works well