?
Solved

oracle procedure that has to be run as a cron job

Posted on 2013-01-09
21
Medium Priority
?
565 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
[X]
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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 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 1000 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 1000 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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
Suggested Courses

752 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