|
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>
|