| Material view auto refresh DBMS_SCHEDULER [message #667815] |
Thu, 18 January 2018 03:53  |
 |
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  |
cookiemonster
Messages: 13904
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
