René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

dbms_scheduler

In Oracle 10g, dbms_scheduler extends the functionality of dbms_job.
dbms_scheduler allows to execute shell scripts (Windows: *.bat files) and executables additionally to the pl/sql code that dbms_job was able to schedule.

Procedures/Functions

add_window_group_member

procedure add_window_group_member (
 group_name                      in varchar2                        ,
 window_list                     in varchar2                        
);

auto_purge

procedure auto_purge (
);

check_sys_privs

function check_sys_privs returns binary_integer (
);

close_window

procedure close_window (
 window_name                     in varchar2                        
);

copy_job

procedure copy_job (
 old_job                         in varchar2                        ,
 new_job                         in varchar2                        
);

create_calendar_string

procedure create_calendar_string (
 frequency                       in binary_integer                  ,
 interval                        in binary_integer                  ,
 bysecond                        in dbms_scheduler                  ,
 byminute                        in dbms_scheduler                  ,
 byhour                          in dbms_scheduler                  ,
 byday_days                      in dbms_scheduler                  ,
 byday_occurrence                in dbms_scheduler                  ,
 bymonthday                      in dbms_scheduler                  ,
 byyearday                       in dbms_scheduler                  ,
 byweekno                        in dbms_scheduler                  ,
 bymonth                         in dbms_scheduler                  ,
 calendar_string                out varchar2                        
);

create_job

procedure create_job (
 job_name                        in varchar2                        ,
 job_type                        in varchar2                        ,
 job_action                      in varchar2                        ,
 number_of_arguments             in binary_integer           default,
 start_date                      in timestamp with time zone  default,
 repeat_interval                 in varchar2                 default,
 end_date                        in timestamp with time zone  default,
 job_class                       in varchar2                 default,
 enabled                         in boolean                  default,
 auto_drop                       in boolean                  default,
 comments                        in varchar2                 default
);

procedure create_job (
 job_name                        in varchar2                        ,
 program_name                    in varchar2                        ,
 schedule_name                   in varchar2                        ,
 job_class                       in varchar2                 default,
 enabled                         in boolean                  default,
 auto_drop                       in boolean                  default,
 comments                        in varchar2                 default
);

procedure create_job (
 job_name                        in varchar2                        ,
 program_name                    in varchar2                        ,
 start_date                      in timestamp with time zone  default,
 repeat_interval                 in varchar2                 default,
 end_date                        in timestamp with time zone  default,
 job_class                       in varchar2                 default,
 enabled                         in boolean                  default,
 auto_drop                       in boolean                  default,
 comments                        in varchar2                 default
);

procedure create_job (
 job_name                        in varchar2                        ,
 schedule_name                   in varchar2                        ,
 job_type                        in varchar2                        ,
 job_action                      in varchar2                        ,
 number_of_arguments             in binary_integer           default,
 job_class                       in varchar2                 default,
 enabled                         in boolean                  default,
 auto_drop                       in boolean                  default,
 comments                        in varchar2                 default
);

create_job_class

procedure create_job_class (
 job_class_name                  in varchar2                        ,
 resource_consumer_group         in varchar2                 default,
 service                         in varchar2                 default,
 logging_level                   in binary_integer           default,
 log_history                     in binary_integer           default,
 comments                        in varchar2                 default
);

create_program

program_type: one of
  • plsql_block
  • stored_procedure
  • executeable 
procedure create_program (
 program_name                    in varchar2                        ,
 program_type                    in varchar2                        ,
 program_action                  in varchar2                        ,
 number_of_arguments             in binary_integer           default,
 enabled                         in boolean                  default,
 comments                        in varchar2                 default
);
program_type: one of
  • plsql_block
  • stored_procedure
  • executeable

create_schedule

procedure create_schedule (
 schedule_name                   in varchar2                        ,
 start_date                      in timestamp with time zone  default,
 repeat_interval                 in varchar2                        ,
 end_date                        in timestamp with time zone  default,
 comments                        in varchar2                 default
);

create_window

procedure create_window (
 window_name                     in varchar2                        ,
 resource_plan                   in varchar2                        ,
 schedule_name                   in varchar2                        ,
 duration                        in interval day to second          ,
 window_priority                 in varchar2                 default,
 comments                        in varchar2                 default
);

procedure create_window (
 window_name                     in varchar2                        ,
 resource_plan                   in varchar2                        ,
 start_date                      in timestamp with time zone  default,
 repeat_interval                 in varchar2                        ,
 end_date                        in timestamp with time zone  default,
 duration                        in interval day to second          ,
 window_priority                 in varchar2                 default,
 comments                        in varchar2                 default
);

create_window_group

procedure create_window_group (
 group_name                      in varchar2                        ,
 window_list                     in varchar2                 default,
 comments                        in varchar2                 default
);

define_anydata_argument

procedure define_anydata_argument (
 program_name                    in varchar2                        ,
 argument_position               in binary_integer                  ,
 argument_name                   in varchar2                 default,
 argument_type                   in varchar2                        ,
 default_value                   in anydata                         ,
 out_argument                    in boolean                  default
);

define_metadata_argument

procedure define_metadata_argument (
 program_name                    in varchar2                        ,
 metadata_attribute              in varchar2                        ,
 argument_position               in binary_integer                  ,
 argument_name                   in varchar2                 default
);

define_program_argument

procedure define_program_argument (
 program_name                    in varchar2                        ,
 argument_position               in binary_integer                  ,
 argument_name                   in varchar2                 default,
 argument_type                   in varchar2                        ,
 default_value                   in varchar2                        ,
 out_argument                    in boolean                  default
);

procedure define_program_argument (
 program_name                    in varchar2                        ,
 argument_position               in binary_integer                  ,
 argument_name                   in varchar2                 default,
 argument_type                   in varchar2                        ,
 out_argument                    in boolean                  default
);

disable

procedure disable (
 name                            in varchar2                        ,
 force                           in boolean                  default
);

drop_job

procedure drop_job (
 job_name                        in varchar2                        ,
 force                           in boolean                  default
);

drop_job_class

procedure drop_job_class (
 job_class_name                  in varchar2                        ,
 force                           in boolean                  default
);

drop_program

procedure drop_program (
 program_name                    in varchar2                        ,
 force                           in boolean                  default
);
Drops a programm that was created with create_program. The parameter program_name must match one of those used in create_program.

drop_program_argument

procedure drop_program_argument (
 program_name                    in varchar2                        ,
 argument_position               in binary_integer                  
);

procedure drop_program_argument (
 program_name                    in varchar2                        ,
 argument_name                   in varchar2                        
);

drop_schedule

procedure drop_schedule (
 schedule_name                   in varchar2                        ,
 force                           in boolean                  default
);

drop_window

procedure drop_window (
 window_name                     in varchar2                        ,
 force                           in boolean                  default
);

drop_window_group

procedure drop_window_group (
 group_name                      in varchar2                        ,
 force                           in boolean                  default
);

enable

procedure enable (
 name                            in varchar2                        
);

evaluate_calendar_string

procedure evaluate_calendar_string (
 calendar_string                 in varchar2                        ,
 start_date                      in timestamp with time zone         ,
 return_date_after               in timestamp with time zone         ,
 next_run_date                  out timestamp with time zone         
);

generate_job_name

function generate_job_name returns varchar2 (
 prefix                          in varchar2                 default
);

get_attribute

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out binary_integer                  
);

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out boolean                         
);

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out date                            
);

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out timestamp                       
);

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out timestamp with time zone         
);

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out timestamp with local time zone         
);

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out interval day to second          
);

procedure get_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                          out varchar2                        
);

get_default_value

function get_default_value returns varchar2 (
 attribute_name                  in varchar2                        
);

get_job_step_cf

function get_job_step_cf returns re$variable_value (
 iec                             in varchar2                        ,
 icn                             in varchar2                        ,
 vname                           in varchar2                        ,
 iev                             in re$nv_list                      
);

get_scheduler_attribute

procedure get_scheduler_attribute (
 attribute                       in varchar2                        ,
 value                          out varchar2                        
);

get_varchar2_value

function get_varchar2_value returns varchar2 (
 a                               in anydata
);

open_window

procedure open_window (
 window_name                     in varchar2                        ,
 duration                        in interval day to second          ,
 force                           in boolean                  default
);

purge_log

procedure purge_log (
 log_history                     in binary_integer           default,
 which_log                       in varchar2                 default,
 job_name                        in varchar2                 default
);

remove_window_group_member

procedure remove_window_group_member (
 group_name                      in varchar2                        ,
 window_list                     in varchar2                        
);

reset_job_argument_value

procedure reset_job_argument_value (
 job_name                        in varchar2                        ,
 argument_position               in binary_integer                  
);

procedure reset_job_argument_value (
 job_name                        in varchar2                        ,
 argument_name                   in varchar2                        
);

resolve_calendar_string

procedure resolve_calendar_string (
 calendar_string                 in varchar2                        ,
 frequency                      out binary_integer                  ,
 interval                       out binary_integer                  ,
 bysecond                       out dbms_scheduler                  ,
 byminute                       out dbms_scheduler                  ,
 byhour                         out dbms_scheduler                  ,
 byday_days                     out dbms_scheduler                  ,
 byday_occurrence               out dbms_scheduler                  ,
 bymonthday                     out dbms_scheduler                  ,
 byyearday                      out dbms_scheduler                  ,
 byweekno                       out dbms_scheduler                  ,
 bymonth                        out dbms_scheduler                  
);

run_job

procedure run_job (
 job_name                        in varchar2                        ,
 use_current_session             in boolean                  default
);

set_attribute

procedure set_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                           in boolean                         
);

procedure set_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                           in varchar2                        
);

procedure set_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                           in date                            
);

procedure set_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                           in timestamp                       
);

procedure set_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                           in timestamp with time zone         
);

procedure set_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                           in timestamp with local time zone         
);

procedure set_attribute (
 name                            in varchar2                        ,
 attribute                       in varchar2                        ,
 value                           in interval day to second          
);

set_attribute_null

procedure set_attribute_null (
 name                            in varchar2                        ,
 attribute                       in varchar2                        
);

set_job_anydata_value

procedure set_job_anydata_value (
 job_name                        in varchar2                        ,
 argument_position               in binary_integer                  ,
 argument_value                  in anydata                         
);

procedure set_job_anydata_value (
 job_name                        in varchar2                        ,
 argument_name                   in varchar2                        ,
 argument_value                  in anydata                         
);

set_job_argument_value

procedure set_job_argument_value (
 job_name                        in varchar2                        ,
 argument_position               in binary_integer                  ,
 argument_value                  in varchar2                        
);

procedure set_job_argument_value (
 job_name                        in varchar2                        ,
 argument_name                   in varchar2                        ,
 argument_value                  in varchar2                        
);

set_scheduler_attribute

procedure set_scheduler_attribute (
 attribute                       in varchar2                        ,
 value                           in varchar2                        
);

stop_job

procedure stop_job (
 job_name                        in varchar2                        ,
 force                           in boolean                  default
);

Scheduling a shell script

dbms_scheduler.create_program(
  program_name        => 'test_shell_script',
  program_action      => '/usr/home/rene/tst_oracle.sh',
  program_type        => 'EXECUTABLE',
  number_of_arguments => 1);

Calendar expresions

  • Yearly
  • Monthly
  • Weekly
  • Daily
  • Hourly
  • Minutely
  • Secondely