CREATE_JOB v17

Use the CREATE_JOB procedure to create a job. The procedure comes in two forms. The first form of the procedure specifies a schedule in the job definition as well as a job action to invoke when the job executes:

CREATE_JOB(
  <job_name> IN VARCHAR2,
  <job_type> IN VARCHAR2,
  <job_action> IN VARCHAR2,
  <number_of_arguments> IN PLS_INTEGER DEFAULT 0,
  <start_date> IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  <repeat_interval> IN VARCHAR2 DEFAULT NULL,
  <end_date> IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  <job_class> IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
  <enabled> IN BOOLEAN DEFAULT FALSE,
  <auto_drop> IN BOOLEAN DEFAULT TRUE,
  <comments> IN VARCHAR2 DEFAULT NULL)

The second form uses a job schedule to specify the schedule on which the job executes and specifies the name of a program to execute when the job runs:

CREATE_JOB(
  <job_name> IN VARCHAR2,
  <program_name> IN VARCHAR2,
  <schedule_name> IN VARCHAR2,
  <job_class> IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
  <enabled> IN BOOLEAN DEFAULT FALSE,
  <auto_drop> IN BOOLEAN DEFAULT TRUE,
  <comments> IN VARCHAR2 DEFAULT NULL)

Parameters

job_name

job_name specifies the optionally schema-qualified name of the job being created.

job_type

job_type specifies the type of job. The current implementation of CREATE_JOB supports a job type of PLSQL_BLOCK or STORED_PROCEDURE.

job_action

• If job_type is PLSQL_BLOCK, job_action specifies the content of the PL/SQL block to invoke when the job executes. The block must be terminated with a semi-colon (;).

• If job_type is STORED_PROCEDURE, job_action specifies the optionally schema-qualified name of the procedure.

number_of_arguments

number_of_arguments is an integer value that specifies the number of arguments expected by the job. The default is 0.

start_date

start_date is a TIMESTAMP WITH TIME ZONE value that specifies the first time that the job is scheduled to execute. The default value is NULL, indicating to schedule the job to execute when the job is enabled.

repeat_interval

repeat_interval is a VARCHAR2 value that specifies how often the job repeats. If you don't specify a repeat_interval, the job executes only once. The default value is NULL.

end_date

end_date is a TIMESTAMP WITH TIME ZONE value that specifies a time after which the job no longer executes. If you specify a date, the end_date must be after start_date. The default value is NULL.

If you don't specify an end_date and you do specify a repeat_interval, the job repeats indefinitely until you disable it.

program_name

program_name is the name of a program for the job to execute.

schedule_name

schedule_name is the name of the schedule associated with the job.

job_class

job_class is accepted for compatibility and ignored.

enabled

enabled is a Boolean value that specifies if the job is enabled when created. By default, a job is created in a disabled state, with enabled set to FALSE. To enable a job, specify a value of TRUE when creating the job, or enable the job with the DBMS_SCHEDULER.ENABLE procedure.

auto_drop

The auto_drop parameter is accepted for compatibility and is ignored. By default, a job's status is changed to DISABLED after the time specified in end_date.

comments

Use the comments parameter to specify a comment about the job.

Example

This example shows a call to the CREATE_JOB procedure:

EXEC
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'update_log',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN INSERT INTO my_log VALUES(current_timestamp);
                        END;',
    start_date      => '01-JUN-15 09:00:00.000000',
    repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=17;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'This job adds a row to the my_log table.');

The code fragment creates a job named update_log that executes each weeknight at 5:00. The job executes a PL/SQL block that inserts the current timestamp into a logfile (my_log). Since no end_date is specified, the job executes until disabled by the DBMS_SCHEDULER.DISABLE procedure.