Solved

To schedule a job in Oracle 8i

Posted on 2007-11-20
10
3,557 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
[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
  • 4
  • 3
  • 3
10 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 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 250 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
Independent Software Vendors: 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!

 
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 250 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

624 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