Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create DBMS_Jobs in PL SQL

User_9XA6OJan 21 2019 — edited Jan 22 2019

Hello Team,

Previously, I have created DBMS_JOB using the following block:

DECLARE

jobno NUMBER:=9999;

BEGIN

dbms_job.submit(jobno, 'sys.DLS_REFRESH;', SYSDATE+1/24, 'sysdate+1/24');

END;

where DLS_REFRESH is my procedure name and job has been created successfully.

Now, I have to create the same job to refresh materialized view as we have already created some DBMS_JOBS for the same.

I have to use the following code to enter into the above  "dbms_job.submit" to create the job successfully.

DBMS_MVIEW.REFRESH(<mview-name>, METHOD => 'c', atomic_refresh => FALSE );

OR :

BEGIN

DBMS_MVIEW.REFRESH(LIST => 'STOCK_KBR_SLABS_HOTBANDS_MV',METHOD => 'C',REFRESH_AFTER_ERRORS => FALSE,PARALLELISM  => 32,

      ATOMIC_REFRESH => FALSE,NESTED => FALSE);

END;

Please guide me for the same, I have tried many ways to add above two but was unable to succeed.

Thanks

Comments
Post Details
Added on Jan 21 2019
9 comments
1,350 views