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!
After SQL - Refresh View Failing
Moderators: chulett, rschirm, roy
After SQL - Refresh View Failing
Vaibhav
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Choose a job you love, and you will never have to work a day in your life. - Confucius
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
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
Vaibhav
-
- Premium Member
- Posts: 376
- Joined: Sat Jan 07, 2012 12:25 pm
- Location: Piscataway
Hi Vaibhav,
You need to quite simply call the unnamed block in the after-sql in the below format -
I tested it and it works well
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;
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.