Solved

To schedule a job in Oracle 8i

Posted on 2007-11-20
10
3,449 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 142

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
 
LVL 142

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

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

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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
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.
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now