Solved

oracle procedure that has to be run as a cron job

Posted on 2013-01-09
21
521 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
Comment Utility
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
Comment Utility
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
Comment Utility
cron job as we are still in 9i and also no need of loop as you mentioned. Thanks for the suggestion.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
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
Comment Utility
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
thanks
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> 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
Comment Utility
that update worked. I tested in my test database. I will aslo try dbms job
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
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 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
>>>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
oh ok. Got it.
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
yes, that looks right
0
 
LVL 6

Author Comment

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

Author Closing Comment

by:anumoses
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 56
Need help on Index -Oracle -Challenging question 5 43
oracle query help 36 64
PL/SQL - Leading zeros 7 39
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now