Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle CREATE_JOB Procedure
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Creating Oracle Jobs

Jobs are what the scheduler is all about.  They are created using the create_job procedure, which is overloaded and allows a job to be defined in one of four ways:

  • Completely self-contained with the program and schedule defined inline

  • Referencing both a predefined program and schedule

  • Referencing a predefined program, but with an inline schedule

  • Referencing a predefined schedule, but with an inline program

The following code examples rely on the previously defined programs and schedules to show how the overloads of the create_job  procedure are used.

 

BEGIN

  -- Job defined entirely by the CREATE JOB procedure.

  DBMS_SCHEDULER.create_job (

    job_name        => 'test_full_job_definition',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'freq=hourly; byminute=0',

    end_date        => NULL,

    enabled         => TRUE,

    comments        => 'Job defined entirely by the CREATE JOB procedure.');

END;

/

BEGIN

  -- Job defined by an existing program and schedule.

  DBMS_SCHEDULER.create_job (

    job_name      => 'test_prog_sched_job_definition',

    program_name  => 'test_plsql_block_prog',

    schedule_name => 'test_hourly_schedule',

    enabled       => TRUE,

    comments      => 'Job defined by an existing program and schedule.');

END;

/

BEGIN

  -- Job defined by an existing program and inline schedule.

  DBMS_SCHEDULER.create_job (

    job_name        => 'test_prog_job_definition',

    program_name    => 'test_plsql_block_prog',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'freq=hourly; byminute=0',

    end_date        => NULL,

    enabled         => TRUE,

    comments        => 'Job defined by existing program and inline schedule.');

END;

/

BEGIN

  -- Job defined by existing schedule and inline program.

  DBMS_SCHEDULER.create_job (

     job_name      => 'test_sched_job_definition',

     schedule_name => 'test_hourly_schedule',

     job_type      => 'PLSQL_BLOCK',

     job_action    => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',

     enabled       => TRUE,

     comments      => 'Job defined by existing schedule and inline program.');

END;

/

 

The generate_job_name function can be used to generate a unique name for a job.

 

FUNCTION generate_job_name (

   prefix        IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2

 

A sequence number is appended to the specified job name prefix to guarantee uniqueness.  If the prefix is not specified, a standard prefix is used.  The query below shows how it can be used:

 

column job_name_1 format a20

column job_name_2 format a20

 

select

   DBMS_SCHEDULER.generate_job_name ('test_job') as job_name_1,

   DBMS_SCHEDULER.generate_job_name as job_name_2

from

   dual;

 

JOB_NAME_1           JOB_NAME_2

-------------------- --------------------

TEST_JOB6            JOB$_7

 

The figures below show the Create Job (General) and Create Job (Schedule) screens, respectively.  These provide a web-based alternative to the create_job procedure.

 

Figure 11.18 – OEM DB Control: Create Job (General)

 

Figure 11.19 – OEM DB Control: Create Job (Schedule)

 

Information about jobs can be displayed using the dba_scheduler_jobs view.  The following script uses this view to display information about currently defined jobs.

 

jobs_10g.sql

 

-- *************************************************

-- Parameters:

--    1) Specific USERNAME or ALL which doesn't limit output.

-- *****************************************************************

 

set verify off

 

select

   owner,

   job_name,

   job_class,

   enabled,

   next_run_date,

   repeat_interval

from

   dba_scheduler_jobs

where

   owner = decode(upper('&1'), 'ALL', owner, upper('&1'))

;

 

The output of the jobs_10g.sql script for the current user is displayed below.

 

SQL> @jobs_10g job_user

 

OWNER                   JOB_NAME                       JOB_CLASS                     ENABLE

------------------------------ ------------------------------ -----------------------------

NEXT_RUN_DATE

---------------------------------------------------------------------------

REPEAT_INTERVAL

--------------------------------------------------------------------

 

JOB_USER                TEST_FULL_JOB_DEFINITION       DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.08.900000 +01:00

freq=hourly; byminute=0

 

JOB_USER                TEST_PROG_SCHED_JOB_DEFINITION DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.16.200000 +01:00

 

JOB_USER                TEST_PROG_JOB_DEFINITION       DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.09.600000 +01:00

freq=hourly; byminute=0

 

JOB_USER                TEST_SCHED_JOB_DEFINITION      DEFAULT_JOB_CLASS              TRUE

22-JUN-04 15.00.16.200000 +01:00

 

When the test_stored_procedure_prog  program is defined, a default argument value is specified.  The argument values of jobs that access predefined programs can be manipulated using the following procedures:

 

PROCEDURE set_job_argument_value (

  job_name                IN VARCHAR2,

  argument_position       IN PLS_INTEGER,

  argument_value          IN VARCHAR2)

 

PROCEDURE set_job_argument_value (

  job_name                IN VARCHAR2,

  argument_name           IN VARCHAR2,

  argument_value          IN VARCHAR2)

 

PROCEDURE set_job_anydata_value(

  job_name                IN VARCHAR2,

  argument_position       IN PLS_INTEGER,

  argument_value          IN SYS.ANYDATA)

 

PROCEDURE set_job_anydata_value(

  job_name                IN VARCHAR2,

  argument_name           IN VARCHAR2,

  argument_value          IN SYS.ANYDATA)

 

PROCEDURE reset_job_argument_value (

  job_name                IN VARCHAR2,

  argument_position       IN PLS_INTEGER)

 

PROCEDURE reset_job_argument_value (

  job_name                IN VARCHAR2,

  argument_name           IN VARCHAR2)

 

The parameters associated with these procedures and their usage are as follows:

  • job_name - A name that uniquely identifies the job

  • argument_position - The position of the argument in the call specification

  • argument_name - The name of the argument

  • argument_value - The value assigned to the argument

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.