Job number : DBMS_JOB « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_JOB

SQL>
SQL>
SQL> set echo on
SQL>
SQL> create table t ( msg varchar2(80) );

Table created.

SQL>
SQL> create or replace procedure p( p_job in number, p_next_date in OUT date ) as
  2      l_next_date date default p_next_date;
  3  begin
  4      p_next_date := trunc(sysdate)+1+3/24;
  5      insert into t values ( 'Next date was "' || to_char(l_next_date,'dd-mon-yyyy hh24:mi:ss') ||
  6        '" Next date IS ' || to_char(p_next_date,'dd-mon-yyyy hh24:mi:ss') );
  7  end;
  8  /

Procedure created.

SQL>
SQL> variable n number
SQL>
SQL> exec dbms_job.submit( :n, 'p(JOB,NEXT_DATE);' );

PL/SQL procedure successfully completed.

SQL>
SQL> select what, interval,
  2         to_char(last_date,'dd-mon-yyyy hh24:mi:ss') last_date,
  3         to_char(next_date,'dd-mon-yyyy hh24:mi:ss') next_date
  4    from user_jobs
  5   where job = :n
  6  /

WHAT
--------------------------------------------------------------------------------
INTERVAL
--------------------------------------------------------------------------------
LAST_DATE            NEXT_DATE
-------------------- --------------------
p(JOB,NEXT_DATE);
null
                     25-jul-2008 19:22:27


SQL>
SQL> exec dbms_job.run( :n );

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

MSG
--------------------------------------------------------------------------------
Next date was "" Next date IS 26-jul-2008 03:00:00

SQL>
SQL> select what, interval,
  2         to_char(last_date,'dd-mon-yyyy hh24:mi:ss') last_date,
  3         to_char(next_date,'dd-mon-yyyy hh24:mi:ss') next_date
  4    from user_jobs
  5   where job = :n
  6  /

WHAT
--------------------------------------------------------------------------------
INTERVAL
--------------------------------------------------------------------------------
LAST_DATE            NEXT_DATE
-------------------- --------------------
p(JOB,NEXT_DATE);
null
25-jul-2008 19:22:27 26-jul-2008 03:00:00


SQL>
SQL> exec dbms_job.remove( :n );

PL/SQL procedure successfully completed.

SQL> drop table t;

Table dropped.

SQL>