Search notes:

Oracle: DBMS_SCHEDULER

dbms_scheduler is used to manage the scheduler.

Procedures and functions

add_event_queue_subscriber
add_group_member
add_job_email_notification
add_to_incompatibility
add_window_group_member
alter_chain
alter_running_chain
analyze_chain
auto_purge Purge from the logs, based on class and global log_history. (Undocumented)
check_aq_cbk_privs
check_credential
check_sys_privs
close_window
copy_job
create_calendar_string
create_chain Creates a chain.
create_credential Deprecated with Oracle 12c in favor of dbms_credential.create_credential.
create_database_destination
create_event_schedule TODO: Scheduler events
create_file_watcher Creates a file watcher object
create_group Creates a window group, a database destination group or an external destination group.
create_incompatibility
create_job Creates a job
create_jobs Creates multiple jobs in a single transaction.
create_job_class
create_program Creates a program.
create_resource
create_schedule
create_window
create_window_group
define_anydata_argument
define_chain_event_step Compare with define_chain_step
define_chain_rule
define_chain_step Compare with define_chain_event_step
define_metadata_argument
define_program_argument
delete_file
disable Disables a given program, job, chain, window, database destination, external destination, file watcher, group or incompatibility.
disable1_calendar_check
drop_agent_destination
drop_chain
drop_chain_rule
drop_chain_step
drop_credential Deprecated with Oracle 12c
drop_database_destination
drop_file_watcher
drop_group
drop_incompatibility
drop_job
drop_job_class
drop_program
drop_program_argument
drop_resource
drop_schedule
drop_window
drop_window_group
enable
end_detached_job_run
evaluate_calendar_string Evaluate the next run-date for a calendar string (which is used in the parameter repeat_interval of dbms_scheduler.create_schedule)
evaluate_running_chain
file_watch_filter
generate_event_list
generate_job_name
get_agent_info Returns the same information as schagent -status.
get_agent_version
get_attribute
get_chain_rule_action
get_chain_rule_condition
get_default_value
get_file, put_file Retrieves/stores a file from/on a host, compare with dbms_file_transfer.get_file and utl_file. get_file and put_file differ from the equivalent utl_file procedures in that they use credentials and can operate on hosts where only a scheduler agent is installed.
get_job_step_cf
get_scheduler_attribute
get_sys_time_zone_name
get_varchar2_value
is_scheduler_created_agent
open_window Opens a window prematurely.
purge_log
remove_event_queue_subscriber
remove_from_incompatibility
remove_group_member
remove_job_email_notification
remove_window_group_member
reset_job_argument_value
reset_job_argument_value
resolve_calendar_string
resolve_name
run_chain Immediately runs a chain by creating a run-once job.
run_job Runs a job outside of its schedule (for example to test the job).
set_agent_registration_pass
set_attribute Sets or modifes a scheduler's object's attribute. Compare with set_scheduler_attribute.
set_attribute_null
set_job_anydata_value
set_job_argument_value
set_job_attributes
set_resource_constraint
set_scheduler_attribute Compare with set_attribute.
show_errors
stime
stop_job
submit_remote_external_job

Creating a program, a schedule and a job

drop   table tq84_table purge;
create table tq84_table (
  tm   date,
  txt  varchar2(50)
);
Github repository Oracle-Patterns, path: /Installed/dbms/scheduler/program_schedule_job/table.sql
create or replace package tq84_pkg as -- {

  procedure proc;

end tq84_pkg; -- }
/

create or replace package body tq84_pkg as -- {

  procedure proc is -- {
  begin
    insert into tq84_table values (sysdate, 'Inserted from tq84_pkg.proc');
  end proc; -- }

end tq84_pkg; -- }
/
Github repository Oracle-Patterns, path: /Installed/dbms/scheduler/program_schedule_job/package.plsql
begin -- { Creating program, schedule and job

  dbms_scheduler.create_program (
    program_name         => 'FOO_PROG', 
    program_type         => 'STORED_PROCEDURE', 
    program_action       => 'tq84_pkg.proc',
    number_of_arguments  =>  0, --  Use dbms_scheduler.define_program_argument if not 0.
    enabled              =>  true,
    comments             => 'Scheduling example'
  );
  -- select enabled from user_scheduler_programs where program_name = 'FOO_PROG';

  dbms_scheduler.create_schedule (
    schedule_name   => 'FOO_SCHEDULE',
    start_date      =>  systimestamp,
    repeat_interval => 'freq=minutely; bysecond=30',
    end_date        =>  null,
    comments        => 'Minute Schedule'
  );
  -- select start_date, repeat_interval, end_date from user_scheduler_schedules where schedule_name = 'FOO_SCHEDULE';

  dbms_scheduler.create_job (
    job_name      => 'FOO_JOB',
    program_name  => 'FOO_PROG',
    schedule_name => 'FOO_SCHEDULE',
    enabled       =>  true,
    comments      => 'Job, combining FOO_SCHEDULE with FOO_PROG'
);
  -- select enabled from user_scheduler_jobs where job_name = 'FOO_JOB';

  -- select log_id, log_date, status from user_scheduler_job_log where job_name = 'FOO_JOB';

end; -- }
/
Github repository Oracle-Patterns, path: /Installed/dbms/scheduler/program_schedule_job/create.plsql
begin -- { Cleaning up

  begin
    dbms_scheduler.drop_job      ('FOO_JOB'     );
  exception when others then
     if sqlcode != -27475 then
  --
  -- Exception -27475 is raised if job does not exist.
  -- Do nothing if job does not exists, else
  -- raise error
  --
        raise;
     end if;
  end;

  begin
    dbms_scheduler.drop_schedule ('FOO_SCHEDULE');
  exception when others then
     if sqlcode != -27476 then
     -- Similar logic as above
        raise;
     end if;
  end;

  begin
    dbms_scheduler.drop_program  ('FOO_PROGRAM' );
  exception when others then
     if sqlcode != -27476 then
     -- Similar logic as above
        raise;
     end if;
  end;

end; -- }
/

drop table    tq84_table;
drop package  tq84_pkg;
Github repository Oracle-Patterns, path: /Installed/dbms/scheduler/program_schedule_job/clean_up.plsql

Object/table types

Object type Description Table type
jobarg Represents an argument in a batch of job arguments. jobarg_array
job_definition Represents a job in a batch of jobs, used by the dbms_scheduler.create_job procedure job_definition_array
jobattr Represents a job attribute in a batch of job attributes. jobattr_array
scheduler$_step_type Used by dbms_scheduler.run_chain scheduler$_step_type_list
scheduler$_event_info The datatype of the Scheduler event queue sys.scheduler$_event_queue
scheduler_filewatcher_result
scheduler_filewatcher_request

Logging levels

Logging levels can be assigned to job classes.
dbms_scheduler.logging_off Nothing is logged for the jobs in the job class
dbms_scheduler.logging_runs The default: logs information about runs
dbms_scheduler.logging_failed_runs Logs only failed runs
dbms_scheduler.logging_full Logs all operations (including creation, deletaion, alteration, disabling and stopping jobs).

See also

dbms_scheduler has superseded dbms_job and offers more possibilities (for example executing shell scripts/cmd.exe batch files).
Event 10992 enables dbms_job instead of dbms_scheduler.
Scheduler related base tables
The specification for dbms_scheduler is in dbmssch.sql
dbms_scheduler calls $ORACLE_HOME/bin/jssu when credentials are used.
Oracle DBMS PL/SQL packages

Links

This link was helpful.

Index