Solved

oracle procedure that has to be run as a cron job

Posted on 2013-01-09
21
533 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:anumoses
  • 12
  • 8
21 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 38760541
why put it in a cron job?  Oracle has dbms_job and dbms_scheduler


when is this routine supposed to run?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38760551
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
 
LVL 6

Author Comment

by:anumoses
ID: 38760561
cron job as we are still in 9i and also no need of loop as you mentioned. Thanks for the suggestion.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 38760565
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38760570
0
 
LVL 6

Author Comment

by:anumoses
ID: 38760573
Any examples of dbms_job?.
Also will the update sql do for that drive_id and drive_date?
0
 
LVL 6

Author Comment

by:anumoses
ID: 38760584
thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38760587
>>> 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
 
LVL 6

Author Comment

by:anumoses
ID: 38760614
that update worked. I tested in my test database. I will aslo try dbms job
0
 
LVL 6

Author Comment

by:anumoses
ID: 38760631
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
 
LVL 6

Author Comment

by:anumoses
ID: 38760647
next_date

can I use to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi'),
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 38760656
>>>> 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
 
LVL 6

Author Comment

by:anumoses
ID: 38760668
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 38760683
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
 
LVL 6

Author Comment

by:anumoses
ID: 38760698
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38760730
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
 
LVL 6

Author Comment

by:anumoses
ID: 38760735
oh ok. Got it.
0
 
LVL 6

Author Comment

by:anumoses
ID: 38760742
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 38760748
yes, that looks right
0
 
LVL 6

Author Comment

by:anumoses
ID: 38760753
perfect. it ran and updated. Thanks for all the help
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 38760802
Thanks a lot
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question