How to Schedule / Execute / Run an Oracle Procedure through DBMS_JOB?


1.   Create a PLSQL procedure
SQL>
create or replace procedure pr_dtl (a in varchar2) is
begin
dbms_output.put_line('Name:'||a);
end;
/
Procedure created.


2.   Schedule the procedure through DBMS_JOB package.
SQL> VARIABLE JOBNO NUMBER;
SQL> EXEC dbms_job.SUBMIT(JOB=>:JOBNO, WHAT=>'PR_DTL(''JAMES'');', NEXT_DATE=>TO_DATE('2012-05-22:07:45:00','YYYY-MM-DD:HH24:MI:SS'), INTERVAL=>'SYSDATE+1');

PL/SQL procedure successfully completed.

SQL> PRINT JOBNO

     JOBNO
----------
         4

3.   If you want you can perform a manual adhoc execution using the following command.
SQL> exec dbms_job.run(job=>4);

PL/SQL procedure successfully completed.

4.   You can check the detail job details in DBA_JOBS dictionary view.
SQL> select JOB, NEXT_DATE, BROKEN, INTERVAL ,LAST_DATE from dba_jobs where job=4;

       JOB NEXT_DATE B INTERVAL                       LAST_DATE
---------- --------- - ------------------------------ ---------
         4 24-MAY-12 N SYSDATE+1                      23-MAY-12

5.   You can remove a job using dbms_jobs.remove
SQL> EXEC dbms_job.remove(job=>4);

PL/SQL procedure successfully completed.

6.   You can modify the existing jobs timing using DBMS_JOB.CHANGE. You have to mark the parameter value as NULL whichever is not required a change.  
SQL>  EXEC DBMS_JOB.CHANGE(JOB=>4,WHAT=>null,NEXT_DATE=>null,INTERVAL=>'SYSDATE+7');

PL/SQL procedure successfully completed.

SQL>  select JOB, NEXT_DATE, BROKEN, INTERVAL ,LAST_DATE from dba_jobs where job=4;

       JOB NEXT_DATE B INTERVAL                       LAST_DATE
---------- --------- - ------------------------------ ---------
         4 24-MAY-12 N SYSDATE+7                      23-MAY-12
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google