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'