Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1532
  • Last Modified:

oracle job execute

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
shashikanth0452
Asked:
shashikanth0452
  • 5
  • 3
  • 2
1 Solution
 
ramkbCommented:

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
 
ramkbCommented:

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
 
Mark GeerlingsDatabase AdministratorCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
ramkbCommented:

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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
ramkbCommented:

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
 
shashikanth0452Author Commented:
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
 
ramkbCommented:

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
 
shashikanth0452Author Commented:
select job, next_date, next_sec, broken, failures, what from user_jobs;

when i execute the above statement

i got no rows selected



0
 
Mark GeerlingsDatabase AdministratorCommented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now