oracle procedure that has to be run as a cron job

I have to take the data from blood_drives table from today to 3 weeks forward.
Update the ht_coord_goal field with the projection field for the dive date and drive id.


Wrote a small procedure. Just wanted to know if this is the way to go.



CREATE OR REPLACE PROCEDURE ht_coord_goal IS

Cursor C1 is
select drive_date,drive_id,projection
  from blood_drives
where to_char(drive_date,'YYYYMMDD')
         between to_char(sysdate,'YYYYMMDD')
       and to_char(sysdate+21,'YYYYMMDD')
   and drive_cancelled is null
   order by drive_date;
   
v_drive_date date;
v_drive_id   number;
v_projection number;
   
   
BEGIN

Open C1;
loop
Fetch C1 into v_drive_date,
                      v_drive_id,
                      v_projection;
Exit when C1%notfound;

 update blood_drives
    set ht_coord_goal = v_projection
  where drive_date = v_drive_date
    and drive_id = v_drive_id;
                                
Close C1;
commit;
                    
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ht_coord_goal;
ht-coord-goal.txt
LVL 6
anumosesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
9i has dbms_job


for example, to run your procedure every day at midnight...



DECLARE
  X NUMBER;
BEGIN
  .DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'ht_coord_goal;'
   ,next_date => trunc(sysdate)+1
   ,interval  => 'trunc(sysdate)+1'
   ,no_parse  => FALSE
  );
COMMIT;
END;
0
 
sdstuberCommented:
why put it in a cron job?  Oracle has dbms_job and dbms_scheduler


when is this routine supposed to run?
0
 
sdstuberCommented:
also, why the loop?  couldn't the whole thing be simplified to just one sql?


update blood_drives
    set ht_coord_goal = projection
where to_char(drive_date,'YYYYMMDD')
         between to_char(sysdate,'YYYYMMDD')
       and to_char(sysdate+21,'YYYYMMDD')
   and drive_cancelled is null;
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anumosesAuthor Commented:
cron job as we are still in 9i and also no need of loop as you mentioned. Thanks for the suggestion.
0
 
Steve WalesSenior Database AdministratorCommented:
0
 
anumosesAuthor Commented:
Any examples of dbms_job?.
Also will the update sql do for that drive_id and drive_date?
0
 
anumosesAuthor Commented:
thanks
0
 
sdstuberCommented:
>>> Any examples of dbms_job?.

yes one is included in my post above.  if you can specify your schedule I'll show you how to implement that with the appropriate interval


>>> Also will the update sql do for that drive_id and drive_date?

your current update doesn't modify those, are they actually relevant to the update given the initial date/cancelled criteria?
0
 
anumosesAuthor Commented:
that update worked. I tested in my test database. I will aslo try dbms job
0
 
anumosesAuthor Commented:
DECLARE
  X NUMBER;
BEGIN
  .DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'ht_coord_goal;'
   ,next_date => trunc(sysdate)+1
   ,interval  => 'trunc(sysdate)+1'
   ,no_parse  => FALSE
  );
COMMIT;
END;

Wanted to run this every day at 10.00 pm. Also can this be added in the procedure itself?

CREATE OR REPLACE PROCEDURE ht_coord_goal IS
   
   
BEGIN

update blood_drives
   set ht_coord_goal = projection
 where to_char(drive_date,'YYYYMMDD')
         between to_char(sysdate,'YYYYMMDD')
             and to_char(sysdate+21,'YYYYMMDD')
   and drive_cancelled is null;
   
commit;
                    
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ht_coord_goal;
0
 
anumosesAuthor Commented:
next_date

can I use to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi'),
0
 
sdstuberConnect With a Mentor Commented:
>>>> can I use to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi'),

yes, and to repeat every day at 10pm after that  use this....

DECLARE
  X NUMBER;
BEGIN
  .DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'ht_coord_goal;'
   ,next_date =>  to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi'),
   ,interval  => 'trunc(sysdate)+1+ 22/24'
   ,no_parse  => FALSE
  );
COMMIT;
END;



the job submission can not be added to the procedure itself.

however you can put your procedure in a package and include an additional procedure that creates the job that way you'll be able to keep them together as a logical unit
0
 
anumosesAuthor Commented:
DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'ht_coord_goal;'
   ,next_date =>  to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi'),
   ,interval  => 'trunc(sysdate)+1+ 22/24'
   ,no_parse  => FALSE
  );

PLS-00103: Encountered the symbol "," when expecting one of the following:

   ( - + case mod new not null others <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   cou
0
 
sdstuberConnect With a Mentor Commented:
DECLARE
    x   NUMBER;
BEGIN
    DBMS_JOB.submit(job        => x,
                    what       => 'ht_coord_goal;',
                    next_date  => TO_DATE('14-Jan-2013 22:00', 'dd-Mon-yyyy hh24:mi'),
                    interval   => 'trunc(sysdate)+1+ 22/24',
                    no_parse   => FALSE
                   );
    COMMIT;
END;
0
 
anumosesAuthor Commented:
got it... compiled.

I added the whole this in one procedure. Hope its right.

CREATE OR REPLACE PROCEDURE ht_coord_goal IS


  X NUMBER;

   
   
BEGIN

update blood_drives
   set ht_coord_goal = projection
 where to_char(drive_date,'YYYYMMDD')
         between to_char(sysdate,'YYYYMMDD')
             and to_char(sysdate+21,'YYYYMMDD')
   and drive_cancelled is null;
   

  DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'ht_coord_goal;'
   ,next_date =>  to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi')
   ,interval  => 'trunc(sysdate)+1+ 22/24'
   ,no_parse  => FALSE
  );

 
   
   
commit;
                    
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ht_coord_goal;
0
 
sdstuberCommented:
nope, that's not right

every time your procedure runs, you'll create a new job.

So tomorrow you'll have 2 jobs set to run at 10pm the next day

Friday you'll have 4 jobs

Saturday you'll have 8 jobs.
and every day it'll just keep doubling

also, since every new job is set with a fixed next date, once you get past that day all of your new jobs will be created "behind schedule", so they'll immediately and thus double again.  this will go infinite - until you consume all your resources and then fail.



as mentioned before don't put the job and the job creation into the same procedure

it doesn't even make sense to try
0
 
anumosesAuthor Commented:
oh ok. Got it.
0
 
anumosesAuthor Commented:
I created a procedure with update statement. And all grants given to the procedure. Now I will test this

DECLARE
    x   NUMBER;
BEGIN
    DBMS_JOB.submit(job        => x,
                    what       => 'ht_coord_goal;',
                    next_date  => TO_DATE('09-Jan-2013 15:00', 'dd-Mon-yyyy hh24:mi'),
                    interval   => 'trunc(sysdate)+1+ 22/24',
                    no_parse   => FALSE
                   );
    COMMIT;
END;

So this will run now and then from tomorrow at specified time.
0
 
sdstuberCommented:
yes, that looks right
0
 
anumosesAuthor Commented:
perfect. it ran and updated. Thanks for all the help
0
 
anumosesAuthor Commented:
Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.