Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

oracle procedure that has to be run as a cron job

Posted on 2013-01-09
21
539 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 74

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 74

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 74

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 74

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 74

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 74

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 74

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 74

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two changes 7 34
Sybase and replication server 13 39
having some issue on pl sql procedure 1 23
update using pipeline function 3 20
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 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