DBMS_SCHEDULER execute a PERL script to load data

Source: Internet
Author: User
Tags perl script
The example uses the oracle11g dbms_scheduler package to execute the perl script to load the data file. The three processes are SET_JOB_ARGUMENT_VALUE and CREATE_JO respectively.

In this example, the oracle 11g dbms_scheduler package is used to execute a perl script to load data files. The three processes are SET_JOB_ARGUMENT_VALUE and CREATE_JO respectively.

1. the example uses the dbms_scheduler package of Oracle 11g to execute a perl script to load data files. Three processes are used: SET_JOB_ARGUMENT_VALUE, CREATE_JOB, and RUN_JOB, the parameters of the three processes are described as follows:

Create_job parameters:

AttributeDescription

Job_name

Name of the job

Job_class

Name of the job class

Job_style

Style of the job:

  • REGULAR

  • LIGHTWEIGHT

  • Program_name

    Name of the program that the job runs

    Job_action

    Inline action of the job. This is either the code for an anonymous PL/SQL block or the name of a stored procedure, external executable, or chain.

    Job_type

    Job action type ('plsql _ Block', 'stored _ PROCEDURE ', 'executable', or 'chain ')

    Schedule_name

    Name of the schedule that specifies when the job has to execute

    Repeat_interval

    Inline time-based schedule

    Schedule_limit

    Maximum delay time between scheduled and actual job start before a job run is canceled

    Start_date

    Start date and time of the job

    End_date

    End date and time of the job

    Event_condition

    Event condition for event-based jobs

    Queue_spec

    File watcher name or queue specification for event-based jobs

    Number_of_arguments

    Number of job arguments

    Arguments

    Array of job arguments

    Job priority

    Job priority

    Job_weight

    * ** Deprecated in Oracle Database 11gR2. Do not change the value of this attribute from the default, which is 1.

    Weight of the job for parallel execution.

    Max_run_duration

    Maximum run duration of the job

    Max_runs

    Maximum number of runs before the job is marked as completed

    Max_failures

    Maximum number of failures tolerated before the job is marked as broken

    Logging_level

    Job logging level

    Restartable

    Indicates whether the job is restartable (TRUE) or not (FALSE)

    Stop_on_window_exit

    Indicates whether the job is stopped when the window that it runs in ends (TRUE) or not (FALSE). Equivalent to thestop_on_window_close job attribute described in the SET_ATTRIBUTE Procedure.

    Raise_events

    State changes that raise events

    Comments

    Comments on the job

    Auto_drop

    If TRUE (the default), indicates that the job shocould be dropped once completed

    Enabled

    Indicates whether the job shoshould be enabled immediately after creating it (TRUE) or not (FALSE)

    Follow_default_timezone

    If TRUE and if the job start_date is null, then when thedefault_timezone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone.

    Parallel_instances

    For event-based jobs only.

    If TRUE, on the arrival of the specified event, the schedcreatcreates a new lightweight job to handle that event, so multiple instances of the same event-based job can run in parallel.

    If FALSE, then an event is discarded if it is raised while the job that handles it is already running,

    Aq_job

    For internal use only

    Instance_id

    The instance ID of the instance that the job must run on

    Credential_name

    The credential to use for a single destination or the default credential for a group of destinations

    Destination

    The name of a single external destination or database destination, or a group name of type external destination or database destination

    Database_role

    In an Oracle Data Guard environment, the database role ('Primary 'or 'logicalstandby') for which the job runs

    Allow_runs_in_restricted_mode

    If TRUE, the job is permitted to run when the database is in restricted mode, provided that the job owner is permitted to log in during this mode

    SET_JOB_ARGUMENT_VALUE parameter:

    ParameterDescription

    Job_name

    The name of the job to be altered

    Argument_name

    The name of the program argument being set

    Argument_position

    The position of the program argument being set

    Argument_value

    The new value to be set for the program argument. To set a non-VARCHAR value, use theSET_JOB_ANYDATA_VALUE procedure.

    RUN_JOB parameters:

    ParameterDescription

    Job_name

    A job name or a comma-separate list of entries, where each is the name of an existing job, optionally preceded by a schema name and dot separator.

    If you specify a multiple-destination job, the job runs on all destinations. In this case, theuse_current_session argument must be FALSE.

    Use_current_session

    This specifies whether or not the job run shoshould occur in the same session that the procedure was invoked from.

    When use_current_session is set to TRUE:

  • The job runs as the user who called RUN_JOB, or in the case of a local external job with a credential, the user named in the credential.

  • You can test a job and see any possible errors on the command line.

  • Run_count, last_start_date, last_run_duration, andfailure_count are not updated.

  • RUN_JOB can be run in parallel with a regularly scheduled job run.

  • When use_current_session is set to FALSE:

  • The job runs as the user who is the job owner.

  • You need to check the job log to find error information.

  • Run_count, last_start_date, last_run_duration, andfailure_count are updated.

  • RUN_JOB fails if a regularly scheduled job is running.

  • For jobs that have a specified destination or destination group, or point to chains or programs with the detached attribute set toTRUE, use_current_session must be FALSE

    In this example, the sqlldr command of the operating system is called to load data files. Therefore, the job_type created in the create_job process is used to implement the job with 'executable'. The meaning of job_type is as follows:

  • 'Plsql _ Block'

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.