?
Solved

oracle job execute

Posted on 2003-03-06
10
Medium Priority
?
1,530 Views
Last Modified: 2008-02-01
I have a procedure by name "getProcdetails". I want a job to create and run the procedure hour by hour.Can anybody tell me how to create job,submit and run.

can anybody explain with an example the same.I am using oracle 9.2.0.3 version

Thanks in advance.

0
Comment
Question by:shashikanth0452
[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
  • 5
  • 3
  • 2
10 Comments
 
LVL 3

Expert Comment

by:ramkb
ID: 8085065

First you need to set the JOB_QUEUE_PROCESSES parameter value to 1 or 2 (more than 0) in your init.ora file and restart your database.  This parameter can also be set dynamically using:
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 2;
while logged in as SYS or SYSTEM.

Once the parameter is set, connect to the userid where you created the procedure and issue the following command(s).

SQL> VARIABLE X NUMBER;
SQL> exec dbms_job.submit(:X, 'getProcdetails;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')';
SQL> COMMIT;

That should do it!

-Ramesh
0
 
LVL 3

Expert Comment

by:ramkb
ID: 8085083

I think i missed a paranthesis at the end of dbms_job statement.  It should be:

SQL> VARIABLE X NUMBER;
SQL> exec dbms_job.submit(:X, 'getProcdetails;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')');
SQL> COMMIT;

- Ramesh
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 300 total points
ID: 8088321
Be aware that when you use Oracle's job queue to schedule jobs to run at regular intervals that the actual start time of the job will tend to drift from when you started it.  Also, if the database is ever down at the next scheduled run time, the job will run when the database restarts, and subsequent runs will then be scheduled relative to the time it actually ran, not when it may have been scheduled.

I'm not sure about the interval that "ramkb" suggested: "trunc(sysdate+1/24,'HH')".  That may work in Oracle9.  It doesn't work in Oracle8.1.7.  I would normally use: "sysdate + 1/24" to have a job run at approximately one-hour intervals.  If you want to fix the next start time to be exactly at a particular time each hour, you will need to use a PL\SQL function to calculate and return that time, then call that function in the "interval" parameter.

I have written a function to do that and can post it here if you like.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 3

Expert Comment

by:ramkb
ID: 8089396

Hi Mark,

No offense intended..  Just want to make my point.  The 'trunc(sysdate+1/24,''HH'')' does work in 8.1.7.  Please note that there is a single quote around trunc(sysdate+1/24,''HH'') and the quotes around HH is two single quotes.  You probably have missed the two quotes around HH.

This exact syntax is delivered by Oracle Corp. in spauto.sql for setting up statspack automatically thro' dbms_job.  I just didn't want to reinvent the wheel.

Let's say if you set this up at 3:30.  The first time it will run at 4:00 and then at regular 1 hour intervals (5:00, 6:00 and so on).

Cheers,
Ramesh
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8089519
Thank you Ramesh for clearing up that syntax question.  I wasn't aware that "trunc" could be used this way with sysdate (or any other date I assume) plus an 'HH' or 'MI' format mask to get the time rounded to that level.

So, that syntax can be used to schedule a job to run on the hour.
0
 
LVL 3

Expert Comment

by:ramkb
ID: 8089766

Oh, you're welcome Mark!  Yeah the date formats works pretty good for both ROUND and TRUNC.

BTW, I just noticed we have a similar profile!

Take care..
-Ramesh
0
 

Author Comment

by:shashikanth0452
ID: 8090083
I have execute the job. But it doesn't execute the procedure. Let me know what could be the problem.

i have set like this also:

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 6;


Thanks in advance to all
0
 
LVL 3

Expert Comment

by:ramkb
ID: 8090158

Can you execute the following query and tell us what the output is:

select job, next_date, next_sec, broken, failures, what from user_jobs;

-Ramesh
0
 

Author Comment

by:shashikanth0452
ID: 8097517
select job, next_date, next_sec, broken, failures, what from user_jobs;

when i execute the above statement

i got no rows selected



0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8104096
That would imply that the job never got created.  Did you ever run:
dbms_job.submit(...[your parameters]);

successfully?

Here is the script I would use in Oracle8.1.7 to add your job to the queue:

set serveroutput on;
declare
  job_no number;
begin
  dbms_job.submit(job_no,'getProcdetails;',trunc(sysdate+1/24,'HH'),'trunc(sysdate+1/24,''HH'') + 1/24');
  dbms_output.put_line('New job: '||to_char(job_no));
end;
/

It may be necessary to do a "commit" after creating the job.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

762 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