Wasn't able to get example to work.
JE, January 03, 2003 - 1:38 pm UTC
Wasn't able to get this to work:
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', decode( sign( 15-to_char(sysdate,'hh24')),
1, trunc(sysdate)+15/24,
trunc( sysdate +
decode( to_char(sysdate,'d'), 6, 3, 1))+7/24
)
,'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
commit;
end;
gives me error:
decode( to_char(sysdate,'d'), 6, 3, 1))+7/24
*
ERROR at line 6:
ORA-06550: line 6, column 19:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
Am I missing something.
January 03, 2003 - 2:34 pm UTC
decode cannot be called from PLSQL, must be done in SQL as the error says.
so, just
select decode( .... ) into l_date_variable from dual;
dbms_job.submit(:n,'x', l_date_variable, 'trunc....' );
Mon and Tuesday only
Jeff, April 30, 2004 - 8:47 am UTC
decode( sign( 15-to_char(sysdate,''hh24'')), 1,
trunc(sysdate)+15/24,
trunc( sysdate+ decode( to_char(dt,''d''), 6, 3,5,4,4,5,3,6, 1))+15/24)
A reader, June 29, 2004 - 11:15 pm UTC
Tom,
Your script is extremely helpful, my question is how is it gonna take care of the 15 min interval for each snapshot period.
Thanks.
June 29, 2004 - 11:57 pm UTC
not sure what you mean.
A reader, June 30, 2004 - 10:36 am UTC
Tom,
What i meant is the time interval between two data points should be 15 min.
You automated it for 7 am and 3 pm M-F, lets say the job is gonna execute command statspack.snap at 7 am shouldn't there be one more job for 7.15 am that executes statspack.snap so there will be 15 min snapshot and viceversa for 3 pm and 3.15pm.
Thanks.
June 30, 2004 - 10:37 am UTC
i gave them what they asked for.
A reader, June 30, 2004 - 12:56 pm UTC
Thanks Tom, will continue this question when i see the submit new question button.
How to submit a bigger interval than 200 Characters
pasko, February 09, 2005 - 11:24 am UTC
Hi Tom,
How can i re-write the interval in the Example below:
i have also checked for this in your book , but i could not find a solution.
I am thinking of writing the case statement in a function , but i have not seen examples where interval in dbms_job is a function:
Here is my Example:
SQL> exec dbms_job.submit(:job , 'begin null ; end ; ' , sysdate -
> ,'CASE WHEN (TO_NUMBER (TO_CHAR ( SYSDATE , ''hh24'')) BETWEEN 10 AND 16) -
> THEN SYSDATE -
> + CASE TO_NUMBER(TO_CHAR ( SYSDATE ,''d'')) -
> WHEN 5 THEN 3 + 1/24 -
> WHEN 6 THEN 2 + 1/24 -
> WHEN 7 THEN 1 + 1/24 -
> ELSE 1 + 1/24 -
> END -
> ELSE -
> TRUNC ( SYSDATE ) -
> + CASE TO_NUMBER (TO_CHAR ( SYSDATE, ''d'')) -
> WHEN 5 THEN 3 + 10/24 -
> WHEN 6 THEN 2 + 10/24 -
> WHEN 7 THEN 1 + 10/24 -
> ELSE 1 + 10/24 -
> END -
> END' ) ;
BEGIN dbms_job.submit(:job , 'begin null ; end ; ' , sysdate ,'CASE WHEN (TO_NUMBER (TO_CHAR ( SYSD
*
FEHLER in Zeile 1:
ORA-01401: Eingefügter Wert zu groß für Spalte
ORA-06512: in "SYS.DBMS_IJOB", Zeile 210
ORA-06512: in "SYS.DBMS_JOB", Zeile 150
ORA-06512: in Zeile 1
February 09, 2005 - 2:52 pm UTC
do you have expert one on one Oracle?
i discuss "custom job scheduling" in there (the procedure can return the next date, the procedure can do whatever it likes to compute this)
also, you can always write a plsql function and have it compute the next date and use it
create or replace function f return date
as
.....
Thanks
pasko, February 09, 2005 - 3:28 pm UTC
Hi Tom,
thanks for your prompt Reply,
Yes , i have both of your Books..
i this case i didn't want to use the a custom next_date in my Procedure..i wanted to use using a custom interval.
i tried the function solution: i think it's working
create or replace function get_interval
return varchar2
as
begin
return to_char(
case_statement from above
, 'DD.MM.YYYY HH24:MI:SS') ;
end ;
My submit is now:
1.
dbms_job.submit(
:job ,
'begin null;end;' ,
sysdate ,
'to_date( get_interval ,'DD.MM.YYYY HH24:MI:SS')'
) ;
i read from dbms_job Package Documentation that oracle actually does a 'select interval into next_date from dual
while computing the next date .
i am still debating wether to use the above interval with the to_date(...) or just the above Function
like:
2.
dbms_job.submit(
:job ,
'begin null;end;' ,
sysdate ,
get_interval --<<--without to_date
) ;
or may be
3.
dbms_job.submit(
:job ,
'begin null;end;' ,
sysdate ,
'a_function_which_returns_a_Date'
) ;
But the another Question:
1.
if i use option number 2 above , should the function Return a date or a Varchar2? i mean, should i use no.1 , 2 or 3 ?
2.
which NLS_* parameters are inserted in column user_jobs.nls_env when i create a JOB?
Do they come from the session which submits the JOB or is it the Database NLS_* parameters ?
February 09, 2005 - 3:48 pm UTC
or your block of code you submit could be:
begin
procedure;
NEXT_DATE:= whatever_you_want.........;
end;
2) query user_jobs/dba_jobs, you'll see everything.....
Error while creating the job
Ramu, December 13, 2006 - 12:18 am UTC
Hi Tom
I am getting error while scheduling the following job
could you please tell me how to over come this
declare
l_job number;
BEGIN
DBMS_JOB.SUBMIT(l_job,
'Tradesnotfedtopca;',
SYSDATE, 'case when to_char(sysdate,'||'''d'''||')=6 and to_char(sysdate,'||'''HH24'''||')=11 and to_char(sysdate,'||'''MI'''||')<=8
then trunc(sysdate+1)+23/24
when to_char(sysdate,'||'''d'''||')=7 and to_char(sysdate,'||'''HH24'''||')=23 and to_char(sysdate,'||'''MI'''||')<=8
then sysdate+10/1440
else sysdate+10/1440
end'
);
COMMIT;
END;
Thanks in Advance
Ram
December 13, 2006 - 7:39 am UTC
my car won't start
why not?
I can say that your string is way overly complex. Why are you concatenating in when you could just "have it there"
Why code:
'case when to_char(sysdate,'||'''d'''||')=6 and
when
'case when to_char(sysdate,''d'')=6 and
would do?????
job
sam, December 19, 2006 - 6:32 pm UTC
Tom:
I have save email parameters to a table and schedule a job to send the email. when I run it gies me an error. do you have an idea what causes that.
SQL> exec dbms_job.run(7);
BEGIN dbms_job.run(7); END;
*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1
December 20, 2006 - 8:04 am UTC
look in your alert log.
jobs
sam, January 05, 2007 - 1:11 pm UTC
How do you explain this. The jobs ran on jan 4 and then supposed to reschedule and run every 24 hours but they did not run on Jan 5, 2007.
JOB LAST_DATE NEXT_DATE B INTERVAL
---------- --------------- --------------- - --------------------
WHAT FAILURES
------------------------------ ----------
192 04-JAN-07 01:14 05-JAN-07 01:00 N trunc(sysdate)+1+1/2
PARTS_REPORTS.PARTS_INV_QTR_LO 0
183 04-JAN-07 01:00 05-JAN-07 01:00 N trunc(sysdate)+1+1/2
REPORT3_LOAD(job); 0
184 04-JAN-07 01:07 05-JAN-07 01:00 N trunc(sysdate)+1+1/2
INV_QTR_LOAD(job); 0
197 03-JAN-07 23:17 04-JAN-07 23:17 N null
THis is my script for scheduling those:
variable l_job number
/
exec dbms_job.submit(:l_job,'REPORT1_LOAD(job);',trunc(sysdate)+1+1/24,'trunc(sysdate)+1+1/24')
/
exec dbms_job.submit(:l_job,'REPORT3_LOAD(job);',trunc(sysdate)+1+1/24,'trunc(sysdate)+1+1/24')
/
exec dbms_job.submit(:l_job,'INV_QTR_LOAD(job);',trunc(sysdate)+1+1/24,'trunc(sysdate)+1+1/24')
/
exec dbms_job.submit(:l_job,'PARTS_REPORTS.PARTS_INV_QTR_LOAD(job);',trunc(sysdate)+1+1/24,'trunc(sysdate)+1+1/24')
/
commit;
January 06, 2007 - 8:38 am UTC
if your job queue processes is set >0, please utilize support.
jobs
sam, January 10, 2007 - 12:28 pm UTC
Tom:
I Run this from sql*plus for windows. Does this confirm the server setting or more of a session setting.
SQL> show parameter job_queue_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
2.
IF you look at job 488 below does it say it failed? Is there a way to track why it failed if Failures=1. Would you be adding an exception handler to the code or there is an oracle log to check that. When I go to sql*plus and do
exec dbms_job.run(488) it ran fine.
JOB LAST_DATE NEXT_DATE B INTERVAL
---------- --------------- --------------- - --------------------
WHAT FAILURES
-------------------------------------------------- ----------
0
488 09-JAN-07 23:00 09-JAN-07 23:02 N trunc(sysdate)+1-1/2
REPORT1_LOAD(job); 1
jobs
Sam, January 17, 2007 - 2:36 pm UTC
Tom:
FOr this you suggested to llok at the laert log. DO you mean the trace file. I could not find anything there. What is that alert log at?
SQL> exec dbms_job.run(137);
BEGIN dbms_job.run(137); END;
*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1
jobs
A reader, March 14, 2007 - 7:23 pm UTC
Tom:
How do you troubleshoot this. I dont even have this line number in my procedure. What is he name of that alert log.
SQL> exec dbms_job.run(23456);
BEGIN dbms_job.run(23456); END;
*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1
March 15, 2007 - 9:16 am UTC
look in your background dump destination (init.ora parameter) to see where the alert log will be - it's name is alert<something>
or, just retrieve the what for job 23456, and run the what and see what you see in sqlplus.
jobs
sam, March 27, 2007 - 11:56 am UTC
Tom:
I use this to schedule a job
exec dbms_job.submit(:l_job,'REPORT1_LOAD(job);',trunc(sysdate)+1-1/24,'trunc(sysdate)+1-1/24')
1. Why oracle keep generating an error?
JOB LAST_DATE NEXT_DATE B INTERVAL
---------- --------------- --------------- - --------------------
WHAT FAILURES
-------------------------------------------------- ----------
23655 27-MAR-07 00:02 27-MAR-07 23:00 N trunc(sysdate)+1-1/2
REPORT1_LOAD(job); 0
*** SESSION ID:(15.100) 2007-03-25 23:06:10.287
*** 2007-03-25 23:06:10.287
ORA-12012: error on auto execute of job 23655
ORA-12005: may not schedule automatic refresh for times in the past
2. THe job is scheduled to run at 11:00 PM but then runs at midnight. why is it sliding to 00:02
3. Note the interval does not show 1/24 like scheduled. Is this because it is showing fixed number of characters.
JOB
----------
INTERVAL
--------------------------------------------------------------------------------
23655
trunc(sysdate)+1-1/24
March 27, 2007 - 12:07 pm UTC
say it is 23:00 (11pm) right now on March 27
27-mar-2007 23:00:00
we trunc that and get:
27-mar-2007 00:00:00
we add one day to that and get
28-mar-2007 00:00:00
and subtract and hour:
27-mar-2007 23:00:00
right smack back where you started from :)
therein lies your problem, you have a date in the past...
ops$tkyte%ORA10GR2> select dt,
2 trunc(dt),
3 trunc(dt)+1,
4 trunc(dt)+1-1/24,
5 trunc(dt)+1+23/24
6 from (
7 select to_date( '27-mar-2007 23:00', 'dd-mon-yyyy hh24:mi' ) dt from dual
8 )
9 /
DT TRUNC(DT) TRUNC(DT)+1 TRUNC(DT)+1-1/24 TRUNC(DT)+1+23/24
-------------------- -------------------- -------------------- -------------------- --------------------
27-mar-2007 23:00:00 27-mar-2007 00:00:00 28-mar-2007 00:00:00 27-mar-2007 23:00:00 28-mar-2007 23:00:00
job
Sam, March 27, 2007 - 4:23 pm UTC
TOm:
This was your code for scheduling a job to run every 24 hours.
The first one is "NExt Date" and the other is "INterval". IN your book, pag 1067 yo uset both the same trunc(sysdate)+1+3/24 so the job runs at 3 AM and then rescedule itself 24 hours later.
You do not think the problem is the slip. THe job was scheduled to run at 11:00 PM. Instead it ran a few seconds after midnight. This changes the trunc(system date) to the new day?
Also, is there a way to prevent the slip?
March 28, 2007 - 11:25 am UTC
no, no IT WAS NOT
I added 3 hours, you SUBTRACTED 1 hour.
Mine works - yours provably did not.
adding three hours is - well - slightly different from subtracting one hour don't you think?
If I added 3 hours to get 3am the next day, obviously you wanted to add 23 hours in order to 11pm the next day....
not subtract 1 hour.
this HAS NOTHING TO DO WITH SLIPPING
this has everything to do with the fact that your function:
trunc(sysdate)+1-1/24
evaluates to 11pm TODAY
my version:
TRUNC(DT)+1+23/24
evaluates to 11pm TOMORROW
Use DBMS_SCHEDULER
Zahir M, March 27, 2007 - 10:34 pm UTC
If you are using 10g , dbms_scheduler is the option to go for .
You can avoid "schedule creep" using DBMS_SCHEDULER.
March 28, 2007 - 11:32 am UTC
just as you can with dbms_job.
I still use dbms_job
a) it is very simple and most times - so is what we need
b) it is transactional - it does NOT commit, the scheduler does (unfortunate)
DBMS_JOB to run statspack.snap but no snapshots generated
Bill, August 02, 2007 - 3:40 pm UTC
Based on the sample given above, I tried to modify your code with the intention to capture snapshot from 15:00 to 23:00 Monday to Friday
variable jobno number;
variable instno number;
declare l_date_variable date;
begin
select instance_number into :instno from v$instance;
Select decode( sign( 23-to_char(sysdate,'hh24')),
1, trunc(sysdate)+23/24,
trunc( sysdate +
decode( to_char(sysdate,'d'), 6, 3, 1))+15/24
) into l_date_variable from dual;
dbms_job.submit(:jobno,'statspack.snap(i_snap_level=>6);', l_date_variable, 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
end;
There are no snapshots generated.
It seems to pick up the right time windows.
TO_CHAR(DT,'DD-MON TO_CHAR(DECODE(SIG
------------------ ------------------
03-AUG-07 07:00:00 03-AUG-07 15:00:00
03-AUG-07 15:00:00 06-AUG-07 07:00:00
06-AUG-07 07:00:00 06-AUG-07 15:00:00
06-AUG-07 15:00:00 07-AUG-07 07:00:00
07-AUG-07 07:00:00 07-AUG-07 15:00:00
07-AUG-07 15:00:00 08-AUG-07 07:00:00
08-AUG-07 07:00:00 08-AUG-07 15:00:00
08-AUG-07 15:00:00 09-AUG-07 07:00:00
09-AUG-07 07:00:00 09-AUG-07 15:00:00
09-AUG-07 15:00:00 10-AUG-07 07:00:00
10-AUG-07 07:00:00 10-AUG-07 15:00:00
10-AUG-07 15:00:00 13-AUG-07 07:00:00
13-AUG-07 07:00:00 13-AUG-07 15:00:00
13-AUG-07 15:00:00 14-AUG-07 07:00:00
14 rows selected.
select log_user, next_date, interval, what from sys.user_jobs;
LOG_USER NEXT_DATE INTERVAL WHAT
-------- ---------- ------------------------- --------------------------------
PERFSTAT 02-AUG-07 trunc(SYSDATE+1/24,'HH') statspack.snap(i_snap_level=>6);
If I simply run statspack.snap, snapshots are generated. Any ideas why it did not work using DBMS_JOB?
Thanks.
August 05, 2007 - 12:44 pm UTC
are jobs configured to run at all
show parameter job_queue_processes
To Bill
Michel cadot, August 05, 2007 - 1:10 pm UTC
I don't see any "commit" in your procedure.
Did you commit?
Regards
Michel
continued from Above
Bill, August 07, 2007 - 3:09 pm UTC
After submitting the request, I kept on searching for clues and found out that the parameters in init.ora was not set for it to run (In that regards, Tom was right).
The parameter job_queue_processs was initially set to 4 but then reset to 0 later inside the init.ora.
Thanks, Tom.
job schedule
Prasanna, August 28, 2007 - 5:21 pm UTC
Hi Tom,
I need to schedule a process (stored procedure execution that send out status email only when a table satisfies a business rule) using dbms_job.
Since I built this send email as a generic procedure, I wrote a wrapper procedure over it that does the above business rule checks and if it satisfies the rules, the invokes the send email procedure.
Else, does nothing.
So far,so good...
Now, There is one more rule that I need to satisfy:
1) I need to start scheduling this wrapper from the first day of every month starting at 00:00 hours and at 1 hour intervals.
This also is fine as I would then use dbms_job by setting:
next_date as trunc(sysdate)
interval as 'trunc(sysdate)+ 1/24'
So far , so good...
Here comes the tricky part...
Once the business rule is satisfied say on the 2nd day nth hour or whenever, the email SP inside the wrapper would get triggered and a mail would be sent...
We want to stop this job from executing again subsequently as the business rules have already been satisfied for this month and we want this same job to schedule itself starting from the first day of the next month...
And this cycle continues...
I am having some confusion here.
We are on 9.2.0.8.
Would be great if you can throw some ideas...
Thanks!
September 04, 2007 - 3:39 pm UTC
have your procedure set the next date.
if business rule is satisfied, next date = add_months(trunc(sysdate,'m'),1)
else next date = sysdate + 1/24
<quote src=Expert one on one Oracle>
Custom SchedulingThere are times, such as the above, where the NEXT_DATE is hard to compute in a simple SQL statement or where the next time the job runs is dependent on some complex procedural set of rules. In this case, we can have the job itself set the next date to run.
If you recall from above, the PLSQL block that runs a job is:
DECLARE
job BINARY_INTEGER := :job;
next_date DATE := :mydate;
broken BOOLEAN := FALSE;
BEGIN
WHAT
:mydate := next_date;
IF broken THEN :b := 1; ELSE :b := 0; END IF;
END;
We have already seen how we can make use of the fact that JOB is available there in the ¿running a job once¿ section. We can use it as a primary key into a parameter table to make maximum use of Shared SQL. Well, we can also make use of the NEXT_DATE variable as well. As you can see in the above block of code, Oracle uses the bind variable :mydate as an INPUT into the routine ¿ to set the NEXT_DATE variable ¿ but it also retrieves this value after WHAT (your procedure) executes. If your procedure happens to modify this value ¿ the value of NEXT_DATE ¿ Oracle will use that as the next date to run the job. As an example, we¿ll set up a small procedure P that will write some informative message to a table T and set its NEXT_DATE:
tkyte@TKYTE816> create table t ( msg varchar2(80) );
Table created.
tkyte@TKYTE816> create or replace
2 procedure p( p_job in number, p_next_date in OUT date )
3 as
4 l_next_date date default p_next_date;
5 begin
6 p_next_date := trunc(sysdate)+1+3/24;
7
8 insert into t values
9 ( 'Next date was "' ||
10 to_char(l_next_date,'dd-mon-yyyy hh24:mi:ss') ||
11 '" Next date IS ' ||
12 to_char(p_next_date,'dd-mon-yyyy hh24:mi:ss') );
13 end;
14 /
Procedure created.
Now, we will schedule this job using the method from ¿running a job once¿ ¿ that is, without an INTERVAL:
tkyte@TKYTE816> variable n number
tkyte@TKYTE816> exec dbms_job.submit( :n, 'p(JOB,NEXT_DATE);' );
PL/SQL procedure successfully completed.
tkyte@TKYTE816> 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 28-apr-2001 18:23:01
In this case, we send the JOB and the NEXT_DATE as parameters to our procedure ¿ these will be supplied by the job queue at runtime. As you can see ¿ this job has not yet run (LAST_DATE is NULL), the INTERVAL is set to null so that the NEXT_DATE will be computed as ¿select null from dual¿. Normally that means the job would run once and be removed from the job queue. However, when this job runs, we¿ll discover:
tkyte@TKYTE816> exec dbms_job.run( :n );
PL/SQL procedure successfully completed.
tkyte@TKYTE816> select * from t;
MSG
---------------------------------------------------------------------
Next date was "" Next date IS 29-apr-2001 03:00:00
tkyte@TKYTE816> 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 28-apr-2001 18:23:01 29-apr-2001 03:00:00
That the NEXT_DATE is filled in ¿ it is the NEXT_DATE computed in the procedure itself ¿ and the job is still in the queue. As long as this job continues to fill in the NEXT_DATE field, it will remain in the job queue. If it ever exits successfully without setting NEXT_DATE ¿ it will be removed from the queue.
This is very useful for those jobs with hard to compute NEXT_DATE values or NEXT_DATE values that depend on data found in other database tables.
</quote>
view on your very first post.
Biswaranjan, July 31, 2012 - 2:59 am UTC
Hi Tom,
I have learnt many things about dbms_job from you site.
I was reading this page and found a tiny error in your very first post logic.
when the time is 6 AM or 5 AM ,it should retrun 7 AM.But it is showing 15.
SO I changed this code
############################
select decode( sign( 15-to_char(sysdate,'hh24')),
1, trunc(sysdate)+15/24,
trunc( sysdate +
decode( to_char(sysdate,'d'), 6, 3, 1))+7/24
) from dual;
#######################
TO
########################
select case when to_char(sysdate,'hh24') >7 and to_char(sysdate,'hh24') <15
then trunc(sysdate)+15/24
else trunc( sysdate +
decode( to_char(sysdate,'d'), 6, 3, 1))+7/24
end case
from dual;
#####################
Can You please confirm ,my understanding is correct or not.
Thanks as always.
July 31, 2012 - 12:38 pm UTC
that is not the logic I implemented - You may implement whatever you want.
I made the choice that if you are running before 3pm, the next one should run at 3pm.
if you want to make it run at 7am if you are earlier then 7am, you may. I just figured if it is running before 3pm, that IS the 7am job.
continuation to my last post
Biswaranjan, July 31, 2012 - 4:55 am UTC
Even By looking deeply into your query ,I found it is not working if the sysdate is "saturday" (it then runs in sunday so i just handled by writting as below).
#################
select case when (to_char(sysdate,'hh24') >7 and to_char(sysdate,'hh24') <15)
and to_char(sysdate,'d') not in(7)
then trunc(sysdate)+15/24
else trunc( sysdate +
decode( to_char(sysdate,'d'), 6, 3,7,2,1))+7/24
end case
from dual;
###########################(mon - firday logic).
Can you please look into ,what i have asked and give your view on that.
Thanks in advance.
Biswaranjan
Submit next job 5 min.s after current job finishes
Satish, November 27, 2012 - 2:54 pm UTC
Tom,
Is it possible to submit a job as follows:
-- Kick off the job to run for every 10 min.s
-- This job runs for 5 min.s
-- Next run has to be 10 min.s after the first run finishes.
Ex: job to run every 10 mins submitted.
1st iteration starts at 10AM
1st iteration finishes at 10:05
2nd iteration starts at 10:10AM
But I want 2nd iteration to start at 10:15 i.e. 10 min.s
after the 1st iteration finishes.
This is a very common requirement as the job takes longer
as the data grows or for whatever other reasons.
Let's say, I don't want to see 2 iterations of this job running at same time.
Is it possible either thru DBMS_JOB or DBMS_SCHEDULER ?
Please let me know.
-Satish
November 29, 2012 - 7:56 am UTC
quote = Expert one on one Oracle
Custom Scheduling
There are times, such as the above, where the NEXT_DATE is hard to compute in a simple SQL statement or where the next time the job runs is dependent on some complex procedural set of rules. In this case, we can have the job itself set the next date to run.
If you recall from above, the PLSQL block that runs a job is:
DECLARE
job BINARY_INTEGER := :job;
next_date DATE := :mydate;
broken BOOLEAN := FALSE;
BEGIN
WHAT
:mydate := next_date;
IF broken THEN :b := 1; ELSE :b := 0; END IF;
END;
We have already seen how we can make use of the fact that JOB is available there in the “running a job once” section. We can use it as a primary key into a parameter table to make maximum use of Shared SQL. Well, we can also make use of the NEXT_DATE variable as well. As you can see in the above block of code, Oracle uses the bind variable :mydate as an INPUT into the routine – to set the NEXT_DATE variable – but it also retrieves this value after WHAT (your procedure) executes. If your procedure happens to modify this value – the value of NEXT_DATE – Oracle will use that as the next date to run the job. As an example, we’ll set up a small procedure P that will write some informative message to a table T and set its NEXT_DATE:
tkyte@TKYTE816> create table t ( msg varchar2(80) );
Table created.
tkyte@TKYTE816> create or replace
2 procedure p( p_job in number, p_next_date in OUT date )
3 as
4 l_next_date date default p_next_date;
5 begin
6 p_next_date := trunc(sysdate)+1+3/24;
7
8 insert into t values
9 ( 'Next date was "' ||
10 to_char(l_next_date,'dd-mon-yyyy hh24:mi:ss') ||
11 '" Next date IS ' ||
12 to_char(p_next_date,'dd-mon-yyyy hh24:mi:ss') );
13 end;
14 /
Procedure created.
Now, we will schedule this job using the method from “running a job once” – that is, without an INTERVAL:
tkyte@TKYTE816> variable n number
tkyte@TKYTE816> exec dbms_job.submit( :n, 'p(JOB,NEXT_DATE);' );
PL/SQL procedure successfully completed.
tkyte@TKYTE816> 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 28-apr-2001 18:23:01
In this case, we send the JOB and the NEXT_DATE as parameters to our procedure – these will be supplied by the job queue at runtime. As you can see – this job has not yet run (LAST_DATE is NULL), the INTERVAL is set to null so that the NEXT_DATE will be computed as “select null from dual”. Normally that means the job would run once and be removed from the job queue. However, when this job runs, we’ll discover:
tkyte@TKYTE816> exec dbms_job.run( :n );
PL/SQL procedure successfully completed.
tkyte@TKYTE816> select * from t;
MSG
---------------------------------------------------------------------
Next date was "" Next date IS 29-apr-2001 03:00:00
tkyte@TKYTE816> 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 28-apr-2001 18:23:01 29-apr-2001 03:00:00
That the NEXT_DATE is filled in – it is the NEXT_DATE computed in the procedure itself – and the job is still in the queue. As long as this job continues to fill in the NEXT_DATE field, it will remain in the job queue. If it ever exits successfully without setting NEXT_DATE – it will be removed from the queue.
This is very useful for those jobs with hard to compute NEXT_DATE values or NEXT_DATE values that depend on data found in other database tables.
next_date equivalent in in dbms_scheduler?
Robert Massey, September 06, 2013 - 3:37 pm UTC
Your technique for setting next_date from within the procedure for a dbms_job job works great. Is there a way to do something similar with dbms_scheduler -- where a job starts 5 minutes after the previous run finishes?
September 10, 2013 - 9:25 am UTC
by not scheduling using a calendar, just have it be a one off job, that schedules the next job five minutes from now... you'd need a when others handler to ensure the job gets scheduled of course - but that when others would rollback, schedule the job, and raise the exception again...
Robert Massey, September 10, 2013 - 11:06 am UTC
Ha! That is such a simple solution, I'm (almost) embarrassed to have asked the question. Now I can stop trying to muck about with the repeat_interval parameter. Thanks!
DBMS jobs one after another
shailesh, January 13, 2014 - 5:04 am UTC
Hi Tom,
I am struggling with a logic : How to run a DBMS job one after another. My requirement is to initiate a PL/SQL code that will initiate a DBMS_JOB.sumbit and after its completion ...need to submit another job.
Any alternative technique is also welcome.
Shailesh
January 13, 2014 - 6:46 pm UTC
either
a) use the dbms_scheduler package, you can set up job chains with that. it does what you want.
b) have the first dbms_job submit the second. have the second submit the third. and so on. make that the last step of the job. If the first job succeeds, the second job will get queued and run - else it won't
DBMS jobs one after another
shailesh, January 16, 2014 - 9:42 am UTC
Hi Tom,
Thanks for reply.I need to run the same job after its compltion. Job is going to pull data using a web service and data will be inserted into the table. So this job will bring data (if available in source in source system). We are trying to build a real time data syncronisation using this job.
Sorry as I didnt make this clear earlier.
Shailesh
January 16, 2014 - 5:56 pm UTC
doesn't matter, if you want A to run after B, have B schedule A as its last step.
even if A=B.
I see nothing here that would change anything I wrote.
A reader, February 14, 2014 - 8:27 pm UTC
Will dbms_job be replaced by the oracle scheduler in future releases?