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,projec
from blood_drives
where to_char(drive_date,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
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
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,projec
from blood_drives
where to_char(drive_date,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
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
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,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
and drive_cancelled is null;
update blood_drives
set ht_coord_goal = projection
where to_char(drive_date,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
and drive_cancelled is null;
ASKER
cron job as we are still in 9i and also no need of loop as you mentioned. Thanks for the suggestion.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Any examples of dbms_job?.
Also will the update sql do for that drive_id and drive_date?
Also will the update sql do for that drive_id and drive_date?
ASKER
thanks
>>> 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?
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?
ASKER
that update worked. I tested in my test database. I will aslo try dbms job
ASKER
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,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
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;
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,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
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;
ASKER
next_date
can I use to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi'),
can I use to_date('14-Jan-2013 22:00','dd-Mon-yyyy hh24:mi'),
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
( 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
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;
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,'YYYYMM
between to_char(sysdate,'YYYYMMDD'
and to_char(sysdate+21,'YYYYMM
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;
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
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
ASKER
oh ok. Got it.
ASKER
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.
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.
yes, that looks right
ASKER
perfect. it ran and updated. Thanks for all the help
ASKER
Thanks a lot
when is this routine supposed to run?