Material view auto refresh DBMS_SCHEDULER [message #667815] |
Thu, 18 January 2018 03:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/334c3263054529b46354eb9aded2c0cc?s=64&d=mm&r=g) |
POGAKU_SANTHOSH
Messages: 39 Registered: April 2017
|
Member |
|
|
Hi team,
I have a materialized view and i need to refresh that mv at a specif time in a day i am using oracle scheduler for that job by using below block.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'update_sales',
job_type => 'STORED_PROCEDURE',
job_action => 'BEGIN DBMS_MVIEW.REFRESH(STELLA_MV); END;',
start_date => '18-JAN-18 07.00.00 PM UTC',
repeat_interval => 'FREQ=DAILY;BYHOUR=15;BYMINUTE=0', /* every other day */
end_date => '18-FEB-18 07.00.00 PM UTC',
auto_drop => FALSE,
job_class => 'batch_update_jobs',
comments => 'My new job');
END;
/
but the db is populating oracle error the job type is assigned as "STORED_PROCEDURE"
ORA-27452: BEGIN DBMS_MVIEW.REFRESH(STELLA_MV); END; is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2
Thanks in advance .
[Updated on: Thu, 18 January 2018 03:56] Report message to a moderator
|
|
|
Re: Material view auto refresh DBMS_SCHEDULER [message #667817 is a reply to message #667815] |
Thu, 18 January 2018 04:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That's because 'BEGIN DBMS_MVIEW.REFRESH(STELLA_MV); END;' isn't a stored procedure
It's a PL/SQL block that happens to call a stored procedure.
It's also invalid as a PL/SQL block - STELLA_MV needs to be wrapped in quotes:
'BEGIN DBMS_MVIEW.REFRESH(''STELLA_MV''); END;'
If you want to use job_type => 'STORED_PROCEDURE' then you need to get rid of the BEGIN/END
|
|
|
|