Solved

Sending mail when dbms_scheduler job fails

Posted on 2011-02-18
11
2,516 Views
Last Modified: 2012-05-11
I want to know how can I implement logic like sending mails or alert through mails with details when ever any job fails in my DB.

0
Comment
Question by:pinkuray
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
If you schedule your JOB using OEM Grid control, you have to mark at options of E-Mail notification for owner,

Please see following articles.

For scheduler: http://www.oracle-base.com/articles/10g/SchedulerEnhancements_10gR2.php#jobs_that_raise_events

You can send email from PL/SQL : http://www.oracle-base.com/articles/9i/EmailFromPLSQL9i.php

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
No need to use UTL_SMTP or UTL_MAIL

To set up email notifications from dbms_scheduler using PL/SQL, check the docs:

DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/scheduse008.htm#ADMIN13302
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
note  DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION  only applies to 11gR2 and above


10g and 11gR1 don't have it, so you would need to implement the emailing as an exception handler within the job, or within a separate job that monitors the main one
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Thanks for the clarification.
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
what if we are going to use in in APEX ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:pinkuray
Comment Utility
the only thing I want a solution is about  sending mail if any jobs fails and this is all abt APEX.

As I am scheduling it through APEX.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you'll have to modify the job after submission with the add notification procedure

the fact that the original submission came from APEX, or any other tool, is immaterial
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
Hello Thanks for all your information.

But I have a small issue. I don't find this procedure ADD_JOB_EMAIL_NOTIFICATION  in my DB.

My database version is 11.1.0.7.0.

as sdstuber: already told it is in 11g R2 but is there any way to include this procedure in my DB?
Or any other way to do this?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
you should be connected as system / sys
0
 
LVL 4

Author Comment

by:pinkuray
Comment Utility
No it's not there i checked it. Please find the details for DBMS_SCHEDULER package in the attached code.
"PACKAGE dbms_scheduler  AUTHID CURRENT_USER AS
"
"
"
"logging_off   CONSTANT PLS_INTEGER := 32;
"
"logging_runs  CONSTANT PLS_INTEGER := 64;
"
"logging_full  CONSTANT PLS_INTEGER := 256;
"
"
"
"-- Program/Job types
"
"-- 'PLSQL_BLOCK'
"
"-- 'STORED_PROCEDURE'
"
"-- 'EXECUTABLE'
"
"
"
"-- Metadata attributes (for a program argument)
"
"-- 'JOB_NAME'
"
"-- 'JOB_OWNER'
"
"-- 'JOB_START'
"
"-- 'WINDOW_START'
"
"-- 'WINDOW_END'
"
"
"
"-- Window Priorities
"
"-- 'HIGH'
"
"-- 'LOW'
"
"
"
"-- Constants for raise events flags
"
"
"
"job_started           CONSTANT PLS_INTEGER := 1;
"
"job_succeeded         CONSTANT PLS_INTEGER := 2;
"
"job_failed            CONSTANT PLS_INTEGER := 4;
"
"job_broken            CONSTANT PLS_INTEGER := 8;
"
"job_completed         CONSTANT PLS_INTEGER := 16;
"
"job_stopped           CONSTANT PLS_INTEGER := 32;
"
"job_sch_lim_reached   CONSTANT PLS_INTEGER := 64;
"
"job_disabled          CONSTANT PLS_INTEGER := 128;
"
"job_chain_stalled     CONSTANT PLS_INTEGER := 256;
"
"job_all_events        CONSTANT PLS_INTEGER := 511;
"
"job_over_max_dur      CONSTANT PLS_INTEGER := 512;
"
"job_run_completed     CONSTANT PLS_INTEGER :=
"
"                        job_succeeded + job_failed + job_stopped;
"
"
"
"/*************************************************************
"
" * Program Administration Procedures
"
" *************************************************************
"
" */
"
"
"
"-- Program attributes which can be used with set_attribute/get_attribute are:
"
"--
"
"-- program_action     - VARCHAR2
"
"--                      This is a string specifying the action. In case of:
"
"--                      'PLSQL_BLOCK': PLSQL code
"
"--                      'STORED_PROCEDURE: name of the database object
"
"--                         representing the type (optionally with schema).
"
"--                      'EXECUTABLE': Full pathname including the name of the
"
"--                         executable, or shell script.
"
"-- program_type       - VARCHAR2
"
"--                      type of program. This must be one of the supported
"
"--                      program types. Currently these are
"
"--                      'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE'
"
"-- comments              - VARCHAR2
"
"--                      an optional comment. This can describe what the
"
"--                      program does, or give usage details.
"
"-- number_of_arguments- PLS_INTEGER
"
"--                      the number of arguments of the program that can be set
"
"--                      by any job using it, these arguments MUST be defined
"
"--                      before the program can be enabled
"
"-- enabled            - BOOLEAN
"
"--                      whether the program is enabled or not. When the program
"
"--                      is enabled, checks are made to ensure that the program
"
"--                      is valid.
"
"
"
"-- Create a new program. The program name can be optionally qualified with a
"
"-- schema. If enabled is set to TRUE, validity checks will be performed and
"
"-- the program will be created in an enabled state if all are passed.
"
"PROCEDURE create_program(
"
"  program_name            IN VARCHAR2,
"
"  program_type            IN VARCHAR2,
"
"  program_action          IN VARCHAR2,
"
"  number_of_arguments     IN PLS_INTEGER DEFAULT 0,
"
"  enabled                 IN BOOLEAN DEFAULT FALSE,
"
"  comments                 IN VARCHAR2 DEFAULT NULL);
"
"
"
"-- Drops an existing program (or a comma separated list of programs).
"
"-- When force is set to false the program must not be
"
"-- referred to by any job.  When force is set to true, any jobs referring to
"
"-- this program will be disabled (same behavior as calling the disable routine
"
"-- on those jobs with the force option).
"
"-- Any argument information that was created for this program will be dropped
"
"-- with the program.
"
"PROCEDURE drop_program(
"
"  program_name            IN VARCHAR2,
"
"  force                   IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Define an argument of a program. All arguments of a program must be defined.
"
"-- If given, the argument name must be unique for this program.
"
"-- Any argument already defined at this position will be overwritten.
"
"-- The argument type must be a valid Oracle or user-defined type.
"
"-- out_argument is reserved for future use. The default and only valid value
"
"-- is FALSE.
"
"PROCEDURE define_program_argument(
"
" program_name            IN VARCHAR2,
"
" argument_position       IN PLS_INTEGER,
"
" argument_name           IN VARCHAR2 DEFAULT NULL,
"
" argument_type           IN VARCHAR2,
"
" default_value           IN VARCHAR2,
"
" out_argument            IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Define an argument of a program without a default value.
"
"-- Any job using this program must set a value to this argument.
"
"-- See other notes for define_program_argument above.
"
"PROCEDURE define_program_argument(
"
" program_name            IN VARCHAR2,
"
" argument_position       IN PLS_INTEGER,
"
" argument_name           IN VARCHAR2 DEFAULT NULL,
"
" argument_type           IN VARCHAR2,
"
" out_argument            IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Define an argument with a default value encapsulated in an ANYDATA.
"
"-- See other notes for define_program_argument above.
"
"PROCEDURE define_anydata_argument(
"
"  program_name            IN VARCHAR2,
"
"  argument_position       IN PLS_INTEGER,
"
"  argument_name           IN VARCHAR2 DEFAULT NULL,
"
"  argument_type           IN VARCHAR2,
"
"  default_value           IN SYS.ANYDATA,
"
"  out_argument            IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Define a special metadata argument for the program. The program developer
"
"-- can retrieve specific scheduler metadata through this argument.
"
"-- Jobs cannot set values for this argument.
"
"-- valid metadata_attributes are: 'COMPLETION_CODE', 'JOB_SUBNAME','JOB_NAME',
"
"-- 'JOB_OWNER', 'JOB_START', 'WINDOW_START', 'WINDOW_END', 'EVENT_MESSAGE'
"
"-- See other notes for define_program_argument above.
"
"PROCEDURE define_metadata_argument(
"
"  program_name            IN VARCHAR2,
"
"  metadata_attribute      IN VARCHAR2,
"
"  argument_position       IN PLS_INTEGER,
"
"  argument_name           IN VARCHAR2 DEFAULT NULL);
"
"
"
"-- drop a program argument either by name or position
"
"PROCEDURE drop_program_argument (
"
"  program_name            IN VARCHAR2,
"
"  argument_position       IN PLS_INTEGER);
"
"
"
"PROCEDURE drop_program_argument (
"
"  program_name            IN VARCHAR2,
"
"  argument_name           IN VARCHAR2);
"
"
"
"/*************************************************************
"
" * Job Administration Procedures
"
" *************************************************************
"
" */
"
"
"
"-- Job attributes which can be used with set_attribute/get_attribute are :
"
"--
"
"-- program_name      - VARCHAR2
"
"--                     The name of a program object to use with this job.
"
"--                     If this is set, job_action, job_type and
"
"--                     number_of_arguments should be NULL
"
"-- job_action        - VARCHAR2
"
"--                     This is a string specifying the action. In case of:
"
"--                      'PLSQL_BLOCK': PLSQL code
"
"--                      'STORED_PROCEDURE': name of the database stored
"
"--                          procedure (C, Java or PL/SQL), optionally qualified
"
"--                          with a schema name).
"
"--                      'EXECUTABLE': Name of an executable of shell script
"
"--                         including the full pathname and any command-line
"
"--                         flags to it.
"
"--                     If this is set, program_name should be NULL.
"
"-- job_type          - VARCHAR2
"
"--                      type of this job. Can be any of:
"
"--                      'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE'
"
"--                     If this is set,program_name should be NULL
"
"-- number_of_arguments- PLS_INTEGER
"
"--                     the number of arguments if the program is inlined. If
"
"--                     this is set, program_name should be NULL.
"
"-- schedule_name     - VARCHAR2
"
"--                     The name of a schedule or window or window group to use
"
"--                     as the schedule for this job.
"
"--                     If this is set, end_date, start_date and repeat_interval
"
"--                     should all be NULL.
"
"-- repeat_interval   - VARCHAR2
"
"--                     either a PL/SQL function returning the next date on
"
"--                     which to run,or calendar syntax expression.
"
"--                     If this is set, schedule_name should be NULL.
"
"-- start_date        - TIMESTAMP WITH TIME ZONE
"
"--                     the original date on which this job was or will be
"
"--                     scheduled to start.
"
"--                     If this is set, schedule_name should be NULL.
"
"-- end_date          - TIMESTAMP WITH TIME ZONE
"
"--                     the date after which the job will no longer run (it will
"
"--                     be dropped if auto_drop is set or disabled with the
"
"--                     state changed to 'COMPLETED' if it is)
"
"--                     If this is set, schedule_name should be NULL.
"
"-- schedule_limit    - INTERVAL DAY TO SECOND
"
"--                     time in minutes after the scheduled time after which a
"
"--                     job that has not been run will be rescheduled. This is
"
"--                     only valid for repeating jobs.
"
"--                     If this is NULL, a job will never
"
"--                     be rescheduled unless it has been run (failed or
"
"--                     successfully)
"
"-- job_class         - VARCHAR2
"
"--                     the class this job is associated with.
"
"-- job_priority      - PLS_INTEGER
"
"--                     the priority of this job relative to other jobs in the
"
"--                     same class. The default is 3 and values should
"
"--                     be 1 and 5 (1 being the highest priority)
"
"-- comments           - VARCHAR2
"
"--                     an optional comment.
"
"-- max_runs          - PLS_INTEGER
"
"--                     the maximum number of consecutive times this job will be
"
"--                     allowed to be run (after this number of consecurtive
"
"--                     times it will be disabled and its state will be changed
"
"--                     to 'COMPLETED'
"
"-- job_weight        - PLS_INTEGER
"
"--                     jobs which include parallel queries should set this to
"
"--                     the number of parallel slaves they expect to spawn
"
"-- logging_level     - PLS_INTEGER
"
"--                     represents how much logging pertaining to
"
"--                     this job should be done
"
"-- max_run_duration  - INTERVAL DAY TO SECOND
"
"--                     the max time for the job to run, if the job runs for
"
"--                     longer than this interval, a job_over_max_dur event
"
"--                     will be raised (the job will not be stopped)
"
"-- max_failures      - PLS_INTEGER
"
"--                     the number of times a job can fail on consecutive
"
"--                     scheduled runs before it is automatically disabled. If
"
"--                     this is set to 0 then the job will keep running no
"
"--                     matter how often it has failed. If a job is
"
"--                     automatically disabled after having failed this number
"
"--                     of times, its state will be changed to BROKEN.
"
"-- instance_stickiness- BOOLEAN
"
"--                      If this option is set to TRUE, then for the first run
"
"--                      of the job the scheduler will choose the instance with
"
"--                      the lightest load to run this job on. Subsequent runs
"
"--                      will use the same instance that the first run used
"
"--                      (unless this instance is down). If this is FALSE then
"
"--                      the scheduler will choose the first available instance
"
"--                      to schedule the job on on all runs.
"
"-- stop_on_window_exit - BOOLEAN
"
"--                       If this job has a window or window group as a schedule
"
"--                       it will be stopped if the associated window closes, if
"
"--                       this boolean attribute is set to TRUE.
"
"-- enabled             - BOOLEAN
"
"--                       whether the job is enabled or not
"
"-- auto_drop           - BOOLEAN
"
"--                       whether the job should be dropped after having
"
"--                       completed
"
"-- restartable         - BOOLEAN
"
"--                       whether the job can be safely restarted (and should be
"
"--                       restarted in case of failure). By default this is set
"
"--                       to FALSE.
"
"
"
"-- create a job in a single call (without using an existing program or
"
"-- schedule).
"
"-- Valid values for job_type and job_action are the same as those for
"
"-- program_type and program_action. If enabled is set TRUE, it will be
"
"-- attempted to enable this job after creating it. If number_of_arguments is
"
"-- set non-zero, values must be set for each of the arguments before enabling
"
"-- the job.
"
"PROCEDURE 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);
"
"
"
"-- create a job using inlined program and inlined event schedule.
"
"-- If enabled is set TRUE, it will be attempted to enable this job after
"
"-- creating it.
"
"-- Values must be set for each argument of the program that does not have a
"
"-- default_value specified (before enabling the job).
"
"-- Note that there are no defaults for event_condition and queue_spec. They
"
"-- must be set explicitly to create an event based job.
"
"PROCEDURE 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,
"
"  event_condition         IN VARCHAR2,
"
"  queue_spec              IN VARCHAR2,
"
"  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);
"
"
"
"-- create a job using a named schedule object and a named program object.
"
"-- If enabled is set TRUE, it will be attempted to enable this job after
"
"-- creating it.
"
"-- Values must be set for each argument of the program that does not have a
"
"-- default_value specified (before enabling the job).
"
"PROCEDURE 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);
"
"
"
"-- create a job using a named program object and an inlined schedule
"
"-- If enabled is set TRUE, it will be attempted to enable this job after
"
"-- creating it.
"
"-- Values must be set for each argument of the program that does not have a
"
"-- default_value specified (before enabling the job).
"
"PROCEDURE create_job(
"
"  job_name                IN VARCHAR2,
"
"  program_name            IN VARCHAR2,
"
"  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);
"
"
"
"-- create a job using named program and inlined event schedule.
"
"-- If enabled is set TRUE, it will be attempted to enable this job after
"
"-- creating it.
"
"-- Values must be set for each argument of the program that does not have a
"
"-- default_value specified (before enabling the job).
"
"-- Note that there are no defaults for event_condition and queue_spec. They
"
"-- must be set explicitly to create an event based job.
"
"PROCEDURE create_job(
"
"  job_name                IN VARCHAR2,
"
"  program_name            IN VARCHAR2,
"
"  start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
"
"  event_condition         IN VARCHAR2,
"
"  queue_spec              IN VARCHAR2,
"
"  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);
"
"
"
"-- create a job using a named schedule object and an inlined program
"
"-- Valid values for job_type and job_action are the same as those for
"
"-- program_type and program_action. If enabled is set TRUE, it will be
"
"-- attempted to enable this job after creating it. If number_of_arguments is
"
"-- set non-zero, values must be set for each of the arguments before enabling
"
"-- the job.
"
"PROCEDURE create_job(
"
"  job_name                IN VARCHAR2,
"
"  schedule_name           IN VARCHAR2,
"
"  job_type                 IN VARCHAR2,
"
"  job_action              IN VARCHAR2,
"
"  number_of_arguments     IN PLS_INTEGER              DEFAULT 0,
"
"  job_class               IN VARCHAR2              DEFAULT 'DEFAULT_JOB_CLASS',
"
"  enabled                 IN BOOLEAN                  DEFAULT FALSE,
"
"  auto_drop               IN BOOLEAN                  DEFAULT TRUE,
"
"  comments                 IN VARCHAR2                 DEFAULT NULL);
"
"
"
"-- Run a job immediately. If use_current_session is TRUE the job is run in the
"
"-- user's current session. If use_current_session is FALSE the job is run in the
"
"-- background by a dedicated job slave.
"
"PROCEDURE run_job(
"
"  job_name                IN VARCHAR2,
"
"  use_current_session     IN BOOLEAN DEFAULT TRUE);
"
"
"
"-- Stop a job or several jobs that are currently running. Job name can also be
"
"-- the name of a job class or a comma-separated list of jobs.
"
"-- If the force option is not specified this will interrupt the job
"
"-- by sending an equivalent of a Ctrl-C to the job. If this fails, an error
"
"-- will be returned.
"
"-- If the force option is specified the job slave will be terminated. Use of
"
"-- the force option requires the MANAGE SCHEDULER system privilege
"
"PROCEDURE stop_job(
"
"  job_name                IN VARCHAR2,
"
"  force                   IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Copy a job. The new_job will contain all the attributes of the old_job,
"
"-- except that it will be created disabled. The state of the old_job will not
"
"-- be altered.
"
"PROCEDURE copy_job(
"
"  old_job                 IN VARCHAR2,
"
"  new_job                 IN VARCHAR2);
"
"
"
"-- Drop a job or several jobs.  Job name can also be
"
"-- the name of a job class or a comma-separated list of jobs.
"
"-- If force is true, all running instances of the job will be stopped by
"
"-- calling stop_job with force set to false. If force is
"
"-- false, dropping a job with running instances will fail.
"
"PROCEDURE drop_job(
"
"  job_name                IN VARCHAR2,
"
"  force                   IN BOOLEAN      DEFAULT FALSE);
"
"
"
"-- Set a value to be passed to one of the arguments of the program (either
"
"-- named, or inlined). If program is inlined, only setting by position is
"
"-- supported. The passed value will override any default value set during
"
"-- definition of the program argument and overwrite any value previously set
"
"-- for this argument position for this job (the previous value will be lost).
"
"PROCEDURE set_job_argument_value(
"
"  job_name                IN VARCHAR2,
"
"  argument_position       IN PLS_INTEGER,
"
"  argument_value          IN VARCHAR2);
"
"
"
"-- This refers to a program argument by its name. It can only be used if the
"
"-- job is using a named program (i.e. program_name points to an existing
"
"-- program). The argument_name used must be the same name defined by the
"
"-- program.
"
"PROCEDURE set_job_argument_value(
"
"  job_name                IN VARCHAR2,
"
"  argument_name           IN VARCHAR2,
"
"  argument_value          IN VARCHAR2);
"
"
"
"-- Same as above but accepts the default value encapsulated in an AnyData
"
"PROCEDURE set_job_anydata_value(
"
"  job_name                IN VARCHAR2,
"
"  argument_position       IN PLS_INTEGER,
"
"  argument_value          IN SYS.ANYDATA);
"
"
"
"-- This refers to a program argument by its name. It can only be used if the
"
"-- job is using a named program (i.e. program_name points to an existing
"
"-- program). The argument_name used must be the same name defined by the
"
"-- program.
"
"PROCEDURE set_job_anydata_value(
"
"  job_name                IN VARCHAR2,
"
"  argument_name           IN VARCHAR2,
"
"  argument_value          IN SYS.ANYDATA);
"
"
"
"-- Clear a previously set job argument value. All job specific value
"
"-- information for this argument is erased. The job will revert back to the
"
"-- default value for this argument as defined by the program (if any).
"
"PROCEDURE reset_job_argument_value(
"
"  job_name                IN VARCHAR2,
"
"  argument_position       IN PLS_INTEGER);
"
"
"
"-- This refers to a program argument by its name. It can only be used if the
"
"-- job is using a named program (i.e. program_name points to an existing
"
"-- program). The argument_name used must be the same name defined by the
"
"-- program.
"
"PROCEDURE reset_job_argument_value(
"
"  job_name                IN VARCHAR2,
"
"  argument_name           IN VARCHAR2);
"
"
"
"/*************************************************************
"
" * Job Class Administration Procedures
"
" *************************************************************
"
" */
"
"
"
"-- Job Class attributes which can be used with set_attribute/get_attribute are:
"
"--
"
"-- resource_consumer_group - VARCHAR2
"
"--                       resource consumer group a class is associated with
"
"-- service             - VARCHAR2
"
"--                       The service the job class belongs to. Default is NULL,
"
"--                       which implies the default service. This should be the
"
"--                       name of the service database object and not the
"
"--                       service name as defined in tnsnames.ora .
"
"-- log_purge_policy    - VARCHAR2
"
"--                       The policy for purging of scheduler log table entries
"
"--                       pertaining to jobs belonging to this class. By default
"
"--                       log table entries are not purged.
"
"-- comments             - VARCHAR2
"
"--                       an optional comment about the class.
"
"
"
"-- Create a job class.
"
"PROCEDURE create_job_class(
"
"  job_class_name          IN VARCHAR2,
"
"  resource_consumer_group IN VARCHAR2     DEFAULT NULL,
"
"  service                 IN VARCHAR2     DEFAULT NULL,
"
"  logging_level           IN PLS_INTEGER  DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
"
"  log_history             IN PLS_INTEGER  DEFAULT NULL,
"
"  comments                IN VARCHAR2     DEFAULT NULL);
"
"
"
"-- Drop a job class (or a comma-separated list of classes). This will return
"
"-- an error if force is set to FALSE and
"
"-- there are still jobs (in any state) that are part of this class.
"
"-- If force is set to TRUE, all jobs that are part of this class will be
"
"-- disabled and their class will be set to the default class.
"
"PROCEDURE drop_job_class(
"
"  job_class_name              IN VARCHAR2,
"
"  force                   IN BOOLEAN DEFAULT FALSE);
"
"
"
"/*************************************************************
"
" * System Window Administration Procedures
"
" *************************************************************
"
" */
"
"
"
"-- System window attributes that can be used with set_attribute/get_attribute
"
"-- are:
"
"--
"
"-- resource_plan       - VARCHAR2
"
"--                       the resource plan to be associated with a window.
"
"--                       When the window opens, the system will switch to
"
"--                       using this resource plan. When the window closes, the
"
"--                       original resource plan will be restored. If a
"
"--                       resource plan has been made active with the force
"
"--                       option, no resource plan switch will occur.
"
"-- window_priority     - VARCHAR2
"
"--                       The priority of the window. Must be one of
"
"--                       'LOW' (default) , 'HIGH'.
"
"-- duration            - INTERVAL DAY TO SECOND
"
"--                       The duration of the window in minutes.
"
"-- schedule_name       - VARCHAR2
"
"--                       The name of a schedule to use with this window. If
"
"--                       this is set, start_date, end_date and repeat_interval
"
"--                       must all be NULL.
"
"-- repeat_interval     - VARCHAR2
"
"--                       A string using the calendar syntax. PL/SQL date
"
"--                       functions are not allowed
"
"--                       If this is set, schedule_name must be NULL
"
"-- start_date          - TIMESTAMP WITH TIME ZONE
"
"--                       next date on which this window is scheduled to open.
"
"--                       If this is set, schedule_name must be NULL.
"
"-- end_date            - TIMESTAMP WITH TIME ZONE
"
"--                       the date after which the window will no longer open.
"
"--                       If this is set, schedule_name must be NULL.
"
"-- enabled             - BOOLEAN
"
"--                       whether the window is enabled or not
"
"-- comments             - VARCHAR2
"
"--                       an optional comment about the window.
"
"-- The below attribute is only visible through the views and not to
"
"-- get_attribute or set_attribute
"
"-- schedule_type     - VARCHAR2
"
"--                     will be one of: 'CALENDAR_STRING', 'NAMED'
"
"
"
"-- Create a system window using a named schedule object. The specified
"
"-- schedule must exist.
"
"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 'LOW',
"
"  comments                 IN VARCHAR2                 DEFAULT NULL);
"
"
"
"-- Create a system window using an inlined schedule.
"
"-- repeat_interval must use the calendar syntax. PL/SQL date functions are not
"
"-- allowed.
"
"PROCEDURE create_window(
"
"  window_name             IN VARCHAR2,
"
"  resource_plan           IN VARCHAR2,
"
"  start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
"
"  repeat_interval         IN VARCHAR2,
"
"  end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
"
"  duration                IN INTERVAL DAY TO SECOND,
"
"  window_priority         IN VARCHAR2                 DEFAULT 'LOW',
"
"  comments                 IN VARCHAR2                 DEFAULT NULL);
"
"
"
"-- Drops a scheduler system window. Window name can also be a window group (in
"
"-- which case all the windows in that window group are dropped) or a
"
"-- comma-separated list of windows. Dropping a window disables all jobs which
"
"-- use the window as a schedule (leaving currently running jobs running). If
"
"-- the window is open, dropping it will attempt to close it first
"
"-- The window is also dropped from any referring window groups.
"
"PROCEDURE drop_window(
"
"  window_name             IN VARCHAR2,
"
"  force                   IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Immediately opens a scheduler window independent of its specified schedule.
"
"-- The window will be opened for the specified duration. If the duration is
"
"-- null, the window will be opened for the duration as specified when the
"
"-- window was created.
"
"-- The next open time of the window is not updated, and will be as determined
"
"-- by the regular scheduled opening.
"
"-- Opening of the window will fail if the DBA has blocked the scheduler from
"
"-- switching to a different resource plan.
"
"-- If force option not specified and a current window is active the operation
"
"-- will fail, unless the window is the current open window.
"
"-- If the current open window equals window_name, the closing time  is set to
"
"-- the system date plus the given duration, i.e. the closing time of the
"
"-- current window is moved up or down, but no jobs are stopped.
"
"PROCEDURE open_window(
"
"  window_name             IN VARCHAR2,
"
"  duration                IN INTERVAL DAY TO SECOND,
"
"  force                   IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Prematurely closes the currently active window. This premature closing
"
"-- of a window will have the same effect as a regular close e.g. any jobs that
"
"-- have a window or a window group as their schedule and were started at the
"
"-- beginning of this window because of that schedule and have indicated that
"
"--they must be stopped on closing of the window, will be stopped.
"
"PROCEDURE close_window(
"
"  window_name             IN VARCHAR2);
"
"
"
"/*************************************************************
"
" * System Window Administration Procedures
"
" *************************************************************
"
" */
"
"
"
"-- enable and disable can be used on window groups. They disable/enable the
"
"-- window group as a whole, not the individual windows in the group.
"
"--
"
"-- member_list refers to a comma-separated list of windows
"
"-- Window groups cannot contain other window groups.
"
"
"
"-- Creates a window group optionally containing windows specified in
"
"-- member_list.
"
"PROCEDURE create_window_group(
"
"  group_name             IN VARCHAR2,
"
"  window_list            IN VARCHAR2 DEFAULT NULL,
"
"  comments               IN VARCHAR2 DEFAULT NULL);
"
"
"
"-- Adds a window (or comma-separated list of windows) to a window group.
"
"-- If a window is already in the window group, it will not be added again.
"
"PROCEDURE add_window_group_member(
"
"  group_name             IN VARCHAR2,
"
"  window_list            IN VARCHAR2);
"
"
"
"-- Removes a window (or comma-separated list of windows) from a window group.
"
"PROCEDURE remove_window_group_member(
"
"  group_name             IN VARCHAR2,
"
"  window_list            IN VARCHAR2);
"
"
"
"-- Drops a window group (does not drop windows that are members of this group)
"
"-- Returns an error when force is set to false and there are jobs whose
"
"-- schedule is the name of the window group. If force is set to true, any jobs
"
"-- whose schedule is the name of the window group will be disabled.
"
"PROCEDURE drop_window_group(
"
"  group_name             IN VARCHAR2,
"
"  force                  IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Get scheduler default time and timezone.
"
"-- This would be used for jobs without a start time specified.
"
"-- Follow default timezone can be set to simulate an object with
"
"-- this attribute set (i.e system windows etc).
"
"FUNCTION stime (
"
"        follow_default_timezone BOOLEAN DEFAULT FALSE)
"
"        RETURN TIMESTAMP WITH TIME ZONE;
"
"
"
"-- Internal.
"
"-- Used for initializing the scheduler default timezone.
"
"FUNCTION get_sys_time_zone_name  RETURN VARCHAR2;
"
"
"
"/*************************************************************
"
" * Schedule Administration Procedures
"
" *************************************************************
"
" */
"
"
"
"-- Schedule attributes which can be used with set_attribute/get_attribute are :
"
"--
"
"-- repeat_interval   - VARCHAR2
"
"--                     an expression using the calendar syntax
"
"-- comments          - VARCHAR2
"
"--                     an optional comment.
"
"-- end_date          - TIMESTAMP WITH TIME ZONE
"
"--                     cutoff date after which the schedule will not specify
"
"--                     any dates
"
"-- start_date        - TIMESTAMP WITH TIME ZONE
"
"--                     start or reference date used by the calendar syntax
"
"--
"
"-- Schedules cannot be enabled and disabled.
"
"
"
"-- Create a named schedule. This must be a valid schedule.
"
"PROCEDURE create_schedule(
"
"  schedule_name           IN VARCHAR2,
"
"  start_date              IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
"
"  repeat_interval         IN VARCHAR2,
"
"  end_date                IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
"
"  comments                IN VARCHAR2                  DEFAULT NULL);
"
"
"
"--- Import helper function.
"
"PROCEDURE disable1_calendar_check;
"
"
"
"-- Create a named event schedule. This must be a valid schedule.
"
"PROCEDURE create_event_schedule(
"
"  schedule_name           IN VARCHAR2,
"
"  start_date              IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
"
"  event_condition         IN VARCHAR2,
"
"  queue_spec              IN VARCHAR2,
"
"  end_date                IN TIMESTAMP WITH TIME ZONE  DEFAULT NULL,
"
"  comments                IN VARCHAR2                  DEFAULT NULL);
"
"
"
"-- Drop a schedule (or comma-separated list of schedules). When force is set
"
"-- to false, and there are jobs or windows
"
"-- that point to this schedule an error will be raised.
"
"-- If force is set to true, any jobs or windows pointing to this schedule will
"
"-- be disabled before the schedule is dropped.
"
"-- Schedules may refer to day calendar schedules in which case no checking
"
"-- occurs. Thus for day calendar drops  force is always assumed true,
"
"-- even if specified as false.
"
"PROCEDURE drop_schedule(
"
"  schedule_name           IN VARCHAR2,
"
"  force                   IN BOOLEAN      DEFAULT FALSE);
"
"
"
"/*************************************************************
"
" * Chain Administration Procedures
"
" *************************************************************
"
" */
"
"
"
"-- Chain attributes which can be used with set_attribute/get_attribute
"
"-- are :
"
"--
"
"-- comments            - VARCHAR2
"
"--                       an optional comment.
"
"-- evaluation_interval - INTERVAL DAY TO SECOND
"
"--                       interval between periodic re-evaluations of a
"
"--                       running chain
"
"--
"
"
"
"-- Creates a chain.
"
"-- Chains are created disabled and must be enabled before use.
"
"PROCEDURE create_chain(
"
"  chain_name              IN VARCHAR2,
"
"  rule_set_name           IN VARCHAR2   DEFAULT NULL,
"
"  evaluation_interval     IN INTERVAL DAY TO SECOND DEFAULT NULL,
"
"  comments                IN VARCHAR2   DEFAULT NULL);
"
"
"
"-- adds or replaces a chain rule
"
"PROCEDURE define_chain_rule(
"
"  chain_name              IN VARCHAR2,
"
"  condition               IN VARCHAR2,
"
"  action                  IN VARCHAR2,
"
"  rule_name               IN VARCHAR2 DEFAULT NULL,
"
"  comments                IN VARCHAR2 DEFAULT NULL);
"
"
"
"-- adds or replaces a chain step and associates it with a program
"
"-- or chain
"
"PROCEDURE define_chain_step(
"
"  chain_name              IN VARCHAR2,
"
"  step_name               IN VARCHAR2,
"
"  program_name            IN VARCHAR2);
"
"
"
"-- adds or replaces a chain step and associates it with an event schedule
"
"PROCEDURE define_chain_event_step(
"
"  chain_name              IN VARCHAR2,
"
"  step_name               IN VARCHAR2,
"
"  event_schedule_name     IN VARCHAR2,
"
"  timeout                 IN INTERVAL DAY TO SECOND DEFAULT NULL);
"
"
"
"-- adds or replaces a chain step and associates it with an inline event
"
"PROCEDURE define_chain_event_step(
"
"  chain_name              IN VARCHAR2,
"
"  step_name               IN VARCHAR2,
"
"  event_condition         IN VARCHAR2,
"
"  queue_spec              IN VARCHAR2,
"
"  timeout                 IN INTERVAL DAY TO SECOND DEFAULT NULL);
"
"
"
"-- drops a chain rule
"
"PROCEDURE drop_chain_rule(
"
"  chain_name              IN VARCHAR2,
"
"  rule_name               IN VARCHAR2,
"
"  force                   IN BOOLEAN  DEFAULT FALSE);
"
"
"
"-- drops a chain step
"
"PROCEDURE drop_chain_step(
"
"  chain_name              IN VARCHAR2,
"
"  step_name               IN VARCHAR2,
"
"  force                   IN BOOLEAN  DEFAULT FALSE);
"
"
"
"-- alters steps of a chain
"
"PROCEDURE alter_chain(
"
"  chain_name              IN VARCHAR2,
"
"  step_name               IN VARCHAR2,
"
"  attribute               IN VARCHAR2,
"
"  value                   IN BOOLEAN);
"
"
"
"-- drops a chain
"
"PROCEDURE drop_chain(
"
"  chain_name              IN VARCHAR2,
"
"  force                   IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- analyzes a chain or a list of steps and rules and outputs a list of
"
"-- chain dependencies
"
"PROCEDURE analyze_chain(
"
"chain_name  IN VARCHAR2,
"
"rules       IN sys.scheduler$_rule_list,
"
"steps       IN sys.scheduler$_step_type_list,
"
"step_pairs  OUT sys.scheduler$_chain_link_list);
"
"
"
"-- alters steps of a running chain
"
"PROCEDURE alter_running_chain(
"
"  job_name                IN VARCHAR2,
"
"  step_name               IN VARCHAR2,
"
"  attribute               IN VARCHAR2,
"
"  value                   IN BOOLEAN);
"
"
"
"-- alters steps of a running chain
"
"PROCEDURE alter_running_chain(
"
"  job_name                IN VARCHAR2,
"
"  step_name               IN VARCHAR2,
"
"  attribute               IN VARCHAR2,
"
"  value                   IN VARCHAR2);
"
"
"
"-- forces immediate evaluation of a running chain
"
"PROCEDURE evaluate_running_chain(
"
"  job_name                IN VARCHAR2);
"
"
"
"-- immediately runs a job pointing to a chain starting with a list of
"
"-- specified steps. The job will be started in the background.
"
"-- If start_steps is NULL, the chain is run from the beginning.
"
"PROCEDURE run_chain(
"
"  chain_name              IN VARCHAR2,
"
"  start_steps             IN VARCHAR2,
"
"  job_name                IN VARCHAR2 DEFAULT NULL);
"
"-- immediately runs a job pointing to a chain starting with the given
"
"-- list of step states. The job will be started in the background.
"
"-- If step_state_list is NULL, the chain is run from the beginning.
"
"PROCEDURE run_chain(
"
"  chain_name              IN VARCHAR2,
"
"  step_state_list         IN SYS.SCHEDULER$_STEP_TYPE_LIST,
"
"  job_name                IN VARCHAR2 DEFAULT NULL);
"
"
"
"/*************************************************************
"
" * Generic Procedures
"
" *************************************************************
"
" */
"
"
"
"-- Disable a program, chain, job, window or window_group.
"
"-- The procedure will NOT return an error if the object was already disabled.
"
"-- It will return an error when force is set to false and:
"
"--   name points to a program and there are jobs/chains pointing to the program
"
"--   name points to a chain and there are jobs/chains pointing to the chain
"
"--   name points to a window or window group and a job has that object as its
"
"--     schedule
"
"-- The only purpose of the force option is to point out dependencies. No
"
"-- dependent objects are altered.
"
"PROCEDURE disable(
"
"  name                   IN VARCHAR2,
"
"  force                  IN BOOLEAN DEFAULT FALSE);
"
"
"
"-- Enable a program, chain, job, window or window group. The procedure will NOT
"
"-- return an error if the object was already enabled.
"
"PROCEDURE enable(
"
"  name                  IN VARCHAR2);
"
"
"
"-- Set an attribute of a scheduler object. Name can be the name of a program,
"
"-- schedule, job, window, or job class. The procedure is overloaded to accept
"
"-- different datatypes.
"
"-- number types are implicitly converted to varchar2
"
"PROCEDURE set_attribute(
"
"  name                  IN VARCHAR2,
"
"  attribute             IN VARCHAR2,
"
"  value                 IN BOOLEAN);
"
"PROCEDURE set_attribute(
"
"  name                  IN VARCHAR2,
"
"  attribute             IN VARCHAR2,
"
"  value                 IN VARCHAR2,
"
"  value2                IN VARCHAR2 DEFAULT NULL);
"
"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 an attribute of a scheduler program to NULL
"
"-- This is necessary because the overloading above does not allow NULL
"
"-- as a valid value.
"
"PROCEDURE set_attribute_null(
"
"  name                  IN VARCHAR2,
"
"  attribute             IN VARCHAR2);
"
"
"
"-- Get the value of an attribute of a program, schedule, job, window, or job
"
"-- class. The procedure is overloaded to support different datatypes for the
"
"-- attribute values: PLS_INTEGER, BOOLEAN,VARCHAR2, all date types.
"
"PROCEDURE get_attribute(
"
"  name                  IN  VARCHAR2,
"
"  attribute             IN  VARCHAR2,
"
"  value                 OUT PLS_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);
"
"PROCEDURE get_attribute(
"
"  name                  IN  VARCHAR2,
"
"  attribute             IN  VARCHAR2,
"
"  value                 OUT VARCHAR2,
"
"  value2                OUT VARCHAR2);
"
"
"
"/*************************************************************
"
" * Special Scheduler Administrative Procedures
"
" *************************************************************
"
" */
"
"
"
"-- There are several scheduler attributes that control the behavior of the
"
"-- scheduler. These have defaults but a DBA may wish to change the default
"
"-- settings or view the current settings. These two functions are provided for
"
"-- this purpose.
"
"-- Even though the scheduler attributes have different types (e.g. strings,
"
"-- numbers) all the values are passed as string literals. The set
"
"-- procedure requires the MANAGE SCHEDULER privilege.
"
"-- This takes effect immediately, but the resulting changes may not be seen
"
"-- immediately.
"
"-- Attributes which may be set are:
"
"-- 'MAX_SLAVE_PROCESSES'(pls_integer), 'DEFAULT_LOG_PURGE_POLICY'(varchar2),
"
"-- 'LOG_HISTORY' (pls_integer)
"
"
"
"-- Set the value of a scheduler attribute. This takes effect immediately,
"
"-- but the resulting changes may not be seen immediately.
"
"PROCEDURE set_scheduler_attribute(
"
"  attribute          IN VARCHAR2,
"
"  value              IN VARCHAR2);
"
"
"
"-- Get the value of a scheduler attribute.
"
"PROCEDURE get_scheduler_attribute(
"
"  attribute          IN VARCHAR2,
"
"  value             OUT VARCHAR2);
"
"
"
"PROCEDURE add_event_queue_subscriber(
"
"  subscriber_name    IN VARCHAR2 DEFAULT NULL);
"
"
"
"PROCEDURE remove_event_queue_subscriber(
"
"  subscriber_name    IN VARCHAR2 DEFAULT NULL);
"
"
"
"-- The following procedure purges from the logs based on the arguments
"
"-- The default is to purge all entries
"
"PROCEDURE purge_log(
"
"  log_history        IN PLS_INTEGER DEFAULT 0,
"
"  which_log          IN VARCHAR2    DEFAULT 'JOB_AND_WINDOW_LOG',
"
"  job_name           IN VARCHAR2    DEFAULT NULL);
"
"
"
"
"
"/*************************************************************
"
" * Auxiliary Functions and Procedures
"
" *************************************************************
"
" */
"
"
"
"-- This function returns a unique name for a job.
"
"-- If prefix is NULL this will be a number from a sequence, otherwise
"
"-- it will be of the form {prefix}N where N is a number from a sequence.
"
"FUNCTION generate_job_name(
"
"  prefix            IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2 ;
"
"
"
"-- These functions are for internal scheduler use.
"
"FUNCTION check_sys_privs RETURN PLS_INTEGER ;
"
"
"
"FUNCTION get_varchar2_value (a SYS.ANYDATA) RETURN VARCHAR2;
"
"
"
"-- The following procedure purges from the logs based on class and global
"
"-- log_history
"
"PROCEDURE auto_purge;
"
"
"
"-- This accepts an attribute name and returns the default value.
"
"-- If the attribute is not recognized it returns NULL.
"
"-- If the attribute is of type BOOLEAN, it will return 'TRUE' or 'FALSE'.
"
"FUNCTION get_default_value (attribute_name VARCHAR2) RETURN VARCHAR2 ;
"
"
"
"-- this is used by chain views to output rule actions
"
"FUNCTION get_chain_rule_action(action_in IN re$nv_list) RETURN VARCHAR2;
"
"
"
"-- this is used by chain views to output rule conditions
"
"FUNCTION get_chain_rule_condition(action_in IN re$nv_list, condition_in IN VARCHAR2)
"
"  RETURN VARCHAR2;
"
"
"
"-- this is used to retrieve the canonicalized object owner or name
"
"FUNCTION resolve_name(
"
"   full_name      IN VARCHAR2,
"
"   default_owner  IN VARCHAR2,
"
"   return_part    IN NUMBER) RETURN VARCHAR2;
"
"
"
"/*************************************************************
"
" * Calendar utility functions for schedule type sched_calendar_string
"
" *************************************************************
"
" */
"
"
"
"TYPE bylist IS VARRAY (256) OF PLS_INTEGER;
"
"
"
"Yearly     Constant Pls_Integer := 1;
"
"Monthly    Constant Pls_Integer := 2;
"
"Weekly     Constant Pls_Integer := 3;
"
"Daily      Constant Pls_Integer := 4;
"
"Hourly     Constant Pls_Integer := 5;
"
"Minutely   Constant Pls_Integer := 6;
"
"Secondly   Constant Pls_Integer := 7;
"
"
"
"
"
"Monday     Constant Integer := 1;
"
"Tuesday    Constant Integer := 2;
"
"Wednesday  Constant Integer := 3;
"
"Thursday   Constant Integer := 4;
"
"Friday     Constant Integer := 5;
"
"Saturday   Constant Integer := 6;
"
"Sunday     Constant Integer := 7;
"
"
"
"-- byday_days contains list of days
"
"-- byday_occurrence contains the corresponding monthly (or yearly)
"
"--   occurrence -5 .. -1,0, 1 .. 5    // 0 meaning any this weekday
"
"
"
"-- Example  BYDAY=-2MO, -1MO, 1MO, TU
"
"-- byday_day = Monday,Monday,Monday,Tuesday
"
"-- byday_orrurrence= -2,-1, 1, 0
"
"
"
"Procedure create_calendar_string(
"
"   frequency         in   pls_integer,
"
"   interval          in   pls_integer,
"
"   bysecond          in   bylist,
"
"   byminute          in   bylist,
"
"   byhour            in   bylist,
"
"   byday_days        in   bylist,
"
"   byday_occurrence  in   bylist,
"
"   bymonthday        in   bylist,
"
"   byyearday         in   bylist,
"
"   byweekno          in   bylist,
"
"   bymonth           in   bylist,
"
"   calendar_string   out  Varchar2);
"
"--
"
"Procedure resolve_calendar_string(
"
"   calendar_string   in   varchar2,
"
"   frequency         out  pls_integer,
"
"   interval          out  pls_integer,
"
"   calendars_used    out  boolean,
"
"   bysecond          out  scheduler$_int_array_type,
"
"   byminute          out  scheduler$_int_array_type,
"
"   byhour            out  scheduler$_int_array_type,
"
"   byday_days        out  scheduler$_int_array_type,
"
"   byday_occurrence  out  scheduler$_int_array_type,
"
"   bydate_y          out  scheduler$_int_array_type,
"
"   bydate_md         out  scheduler$_int_array_type,
"
"   bymonthday        out  scheduler$_int_array_type,
"
"   byyearday         out  scheduler$_int_array_type,
"
"   byweekno          out  scheduler$_int_array_type,
"
"   bymonth           out  scheduler$_int_array_type,
"
"   bysetpos          out  scheduler$_int_array_type);
"
"
"
"
"
"Procedure resolve_calendar_string(
"
"   calendar_string   in   varchar2,
"
"   frequency         out  pls_integer,
"
"   interval          out  pls_integer,
"
"   bysecond          out  bylist,
"
"   byminute          out  bylist,
"
"   byhour            out  bylist,
"
"   byday_days        out  bylist,
"
"   byday_occurrence  out  bylist,
"
"   bymonthday        out  bylist,
"
"   byyearday         out  bylist,
"
"   byweekno          out  bylist,
"
"   bymonth           out  bylist);
"
"
"
"-- Repeat intervals of jobs, windows or schedules are defined using the
"
"-- scheduler's calendar syntax. This procedure evaluates the calendar string
"
"-- and tells you what the next execution date of a job or window will be. This
"
"-- is very useful for testing the correct definition of the calendar string
"
"-- without having to actually schedule the job or window.
"
"--
"
"-- Parameters
"
"-- calendar_string    The to be evaluated calendar string.
"
"-- start_date         The date by which the calendar string becomes valid.
"
"--                    It might also be used to fill in specific items that are
"
"--                    missing from the calendar string. Can optionally be NULL.
"
"-- return_date_after  With the start_date and the calendar string the scheduler
"
"--                    has sufficient information to determine all valid
"
"--                    execution dates. By setting this argument the scheduler
"
"--                    determines which one of all possible matches to return.
"
"--                    When a NULL value is passed for this argument the
"
"--                    scheduler automatically fills in systimestamp as its
"
"--                    value.
"
"-- next_run_date      The first timestamp that matches the calendar string and
"
"--                    start date that occurs after the value passed in for the
"
"--                    return_date_after argument.
"
"
"
"
"
"
"
"-- This procedure can also be used to get multiple steps of the repeat interval
"
"-- by passing the next_run_date returned by one invocation as the
"
"-- return_date_after argument of the next invocation of this procedure.
"
"
"
"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);
"
"
"
"-- Internal function. Do not document.
"
"FUNCTION get_job_step_cf
"
"(
"
"   iec                         VARCHAR2,
"
"   icn                         VARCHAR2,
"
"   vname                       VARCHAR2,
"
"   iev                         SYS.RE$NV_LIST
"
") RETURN SYS.RE$VARIABLE_VALUE;
"
"
"
"FUNCTION generate_event_list(statusvec NUMBER) return VARCHAR2;
"
"
"
END dbms_scheduler;

Open in new window

0
 
LVL 4

Author Closing Comment

by:pinkuray
Comment Utility
I used APEX_MAIL package which help me but thanks for showing me the right direction.

Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now