advertisements
_____________________________________________________________________________________________________________________
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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment