• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 911
  • Last Modified:

How to schedule a job with dba_scheduler_jobs?

Have a stored procedure p1 and want to schedule a job to run it every hour except 3am to 6am with dba_scheduler_jobs? Can any gurus shed some light on it? The DB version is 11gR1
0
jl66
Asked:
jl66
  • 5
  • 4
  • 2
  • +1
5 Solutions
 
slightwv (䄆 Netminder) Commented:
See if this works:

begin
dbms_scheduler.create_job
 (job_name => 'MY_PROC',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'SOME_PROCEDURE',
  start_date=> sysdate,
  repeat_interval=> 'FREQ=DAILY; BYHOUR=0,1,2,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23;',
  enabled=>true);
end;
0
 
jl66Author Commented:
Thanks for the info. I forgot to mentin if the stored procedure takes two parameters: p1('table_name', date)
How to set it up??
0
 
mrjoltcolaCommented:
Use the 'PLSQL_BLOCK' job_type and put inline PLSQL into job_action

job_name        => 'MY_PROC',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN p1('table_name', sysdate); END;',
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
OP_ZaharinCommented:
- if you have Oracle Enterprise Manager installed, you can schedule, manage and monitor the job from the GUI under Oracle Scheduler > Jobs. you can set the schedule options required via the browser instead of command line.

- after you have schedule the job, (if you did not setup EM) you can query to the following views: DBA_SCHEDULER_JOBS for all scheduler job in the database, USER_SCHEDULER_JOBS for jobs own by the current user and ALL_SCHEDULER_JOBS for jobs accessible to the current user.  to display the status log of any schedule job, just add _LOG to each of the above view name eg ALL_SCHEDULER_JOBS_LOG which will display the job name, job owner, date-time executed and status of the job run.

0
 
slightwv (䄆 Netminder) Commented:
>> I forgot to mentin if the stored procedure takes two parameters

Check the docs for:  SET_JOB_ARGUMENT_VALUE

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1011390

There are a lot of examples our there if you search...


DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
             job_name => 'MY_PROC'
            ,argument_position => 1
            ,argument_value => 'SOME_TABLE_NAME'
    );


Hopefully you can get the second parameter...
0
 
jl66Author Commented:
mrjoltcola:
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/77    PLS-00103: Encountered the symbol "t1" when expecting one
         of the following:
         ) , * & = - + < / > at in is mod remainder not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2
         like4 likec between || multiset member submultiset
         The symbol ", was inserted before "t1" to continue.

where t1 line corresponds to job_action      => 'BEGIN p1('table_name', sysdate); END;',
How to deal with ' of ' ?  t1=table_name ???
0
 
slightwv (䄆 Netminder) Commented:
Looks like that error is from the procedure itself.

You might need to double the single quotes to get one.  I'm not sure what the procedure is expecting but if you want to try that method, try(two single quotes, not a double quote):

'BEGIN p1(''table_name'', sysdate); END;'
0
 
jl66Author Commented:
Yes. I did it. No error. However when I queried the view, I I could not see the job I just created.
SELECT job_name,start_date from all_scheduler_jobs;
Do I miss anything???
0
 
jl66Author Commented:
I logged in and created it as sys.
0
 
slightwv (䄆 Netminder) Commented:
You should not create things as sys.  It's a bad practice.

Try dba_scheduler_jobs.
0
 
OP_ZaharinCommented:
- i second slightwv. create a user, grant CREATE JOB privilege to the user and grant other necessary privilege required by the job to run.

- i've run your create job script on my schema and  it's visible on all_scheduler_jobs view.
0
 
jl66Author Commented:
Great help.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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