Home » SQL & PL/SQL » SQL & PL/SQL » Material view auto refresh DBMS_SCHEDULER (oracle 11g)
Material view auto refresh DBMS_SCHEDULER [message #667815] Thu, 18 January 2018 03:53 Go to next message
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 messageGo to next message
cookiemonster
Messages: 13340
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
Re: Material view auto refresh DBMS_SCHEDULER [message #667819 is a reply to message #667817] Thu, 18 January 2018 04:42 Go to previous message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
Thank you for the response. I tried to modify the code as per the guidelines which you have given and its working .

Thank you . Smile

[Updated on: Thu, 18 January 2018 04:44]

Report message to a moderator

Previous Topic: How to replace with null for non alphanumeric characters in Varchar2 field.
Next Topic: Special grouping with SQL
Goto Forum:
  


Current Time: Mon Nov 19 14:26:59 CST 2018