DBMS_SCHEDULER执行PERL脚本加载数据

例子利用oracle 11g 的dbms_scheduler包执行perl脚本加载数据文件,其中主要用到三个过程分别为SET_JOB_ARGUMENT_VALUE,CREATE_JO

1.例子利用Oracle 11g 的dbms_scheduler包执行perl脚本加载数据文件,其中主要用到三个过程分别为SET_JOB_ARGUMENT_VALUE,CREATE_JOB,RUN_JOB三个过程,其中三个过程的参数说明如下:

create_job参数:

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 should be dropped once completed

enabled

Indicates whether the job should 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 Scheduler creates 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参数:

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参数:

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 should 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

由于本例中是调用操作系统的sqlldr命令去实现数据文件的加载,所以要用到create_job过程创建的job_type为'EXECUTABLE'的job去实现,其中job_type含义如下

'PLSQL_BLOCK'