Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

To schedule a job in Oracle 8i

Posted on 2007-11-20
10
Medium Priority
?
3,640 Views
Last Modified: 2013-12-19
I have a procedure say "sp_run_the proc".
I need to run this procedure every day at 1:00 AM.
In a way,i need to schedule a job to run this procedure at fixed time daily.
Can someone help me out as how exactly i could implement this? Not to forget that i am using Oracle 8i.

Thanks in advance.
0
Comment
Question by:koolsyed
  • 4
  • 3
  • 3
10 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 20319611
you will need this:
http://www.psoug.org/reference/dbms_job.html

dbms_job.submit(1, 'owner.sp_run_the_proc', trunc(sysdate) + 1/24, 'trunc(sysdate+1) + 1/24');


and you need to ensure that you have at least 1 job queue process running.
0
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 1000 total points
ID: 20319650
Replace the Package, procedure and parameters below.
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'PACKAGE.PROCEDURE(PARAMETERS);',
next_date => TRUNC(SYSDATE) + 1/24,
interval => 'TRUNC(SYSDATE) + 1/24'
);
COMMIT;
END;

Open in new window

0
 

Author Comment

by:koolsyed
ID: 20326236
Thanks angel and kamdar..

how would i know what number to be given to my job?..
Is there a way..i can check the current list of jobs?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20326269
jinesh_kamdar's suggestion is more flexible, so you don't need to give a number, but jobno will then get the number in return.
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'PACKAGE.PROCEDURE(PARAMETERS);',
next_date => TRUNC(SYSDATE) + 1/24,
interval => 'TRUNC(SYSDATE) + 1/24'
);
dbms_output.put_line(jobno);
COMMIT;
END;

Open in new window

0
 

Author Comment

by:koolsyed
ID: 20326426
Thanks angel..i am tryin out in my dev environment...by running for every 2 mins..
==>
but,can you gimme details as what exactly are next_data and interval?
from what i got from net,interval seems to be clear..but next_data value is bit confusing.
Can you elucidate?
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20326464
DBMS_JOB.SUBMIT Parameters

The DBMS_JOB.SUBMIT procedure adds a new job to the job queue. It accepts five parameters and
returns the number of a job submitted through the OUT parameter JOB. The descriptions of the parameters are listed below.

Note: An exception is raised if the interval does not evaluate to a time in the future.
Parameter  Mode Description
---------------------------
JOB        OUT  Unique identifier of the job
WHAT       IN   PL/SQL code to execute as a job
NEXT_DATE  IN   Next execution date of the job
INTERVAL   IN   Date function to compute the next execution date of a job
NO_PARSE   IN   Boolean flag that indicates whether to parse the job at job submission (the default is false)

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20326477
the "next date" is the first next run. once it run that first date, the parameter interval will be applied to determine the following dates.
0
 

Author Comment

by:koolsyed
ID: 20327253
Thanks..i scheduled it for 2 mins..it works fine...
but vil need to monitor for few days..while i check for 1:00 AM thing..
I am gonna use below query..if u find ne flaw and warn..wud b great.
---Daily at 1 AM
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'PROC_TEST;',
next_date => 'TRUNC(SYSDATE) + 1/24',
interval => 'TRUNC(SYSDATE) + 1/24'
);
dbms_output.put_line(jobno);
COMMIT;
END;

i have put value against next_date in quotes..hope its correct...quotes werent there for examples posted.

One more thing, is there a way i could check the success/failure flag for the job?

hope my nxt posting wud be closure posting of this thread ;-)
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 1000 total points
ID: 20327444
>> i have put value against next_date in quotes..hope its correct...quotes werent there for examples posted
No, its not correct. The NEXT_DATE parameter expects a date-type and so you would have to remove the quotes around it.

Viewing Information on Submitted Jobs

The DBA_JOBS and DBA_JOBS_RUNNING dictionary views display information about jobs in the queue and jobs that have run. To be able to view the dictionary information, users should be granted the SELECT privilege on SYS.DBA_JOBS.

SELECT job, log_user, next_date, next_sec, broken, what
FROM DBA_JOBS;

Use the USER_JOBS data dictionary view to display information about jobs in the queue for you.
0
 

Author Comment

by:koolsyed
ID: 20327513
great,thanks...closing the thread!..
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

926 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