DBMS_SCHEDULER执行PERL脚本加载数据 - 数据库编程

TOP

DBMS_SCHEDULER执行PERL脚本加载数据(二)

2015-07-24 11:37:13 来源: 作者: 【大中小】浏览:16次

rmitted to log in during this mode

Parameter	Description
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.

Parameter

Description

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

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

SET_JOB_ARGUMENT_VALUE参数:

RUN_JOB参数:

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

'PLSQL_BLOCK'
This specifies that the job is an anonymous PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0.
'STORED_PROCEDURE'
This specifies that the job is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported.
'EXECUTABLE'
This specifies that the job is external to the database. External jobs are anything that can be executed from the command line of the operating system. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.
'CHAIN'
This specifies that the job is a chain. Arguments are not supported for a chain, so number_of_arguments must be 0.
2.由于用到dbms_scheduler包创建'EXECUTABLE'类型的job,需要对操作系统用户及数据库用户配置,以ETL(操作系统用户),ETL_TEST(数据库用户)为例进行配置,实验环境为Redhat5.5+ Oracle11G(11.2.3)+Perl(5.8.8)

a.创建操作系统用户

[root@ETL ~]# useradd -d /home/etl/ -m etl

[root@ETL ~]# passwd etl

Changing password for user etl.

New UNIX password:

BAD PASSWORD: it is based on a dictionary word

Retype new UNIX password:

passwd: all authentication tokens updated successfully.

注明:在linux系统中如果没有指定创建用户的组,系统会默认创建一个与用户名一致的用户组

b.配置用户ETL的环境变量(/home/etl/.bash_profile),其中红色字

首页上一页 1 2 3 4 5 6 下一页尾页 2/6/6
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】
分享到:
上一篇：RACCacheFusion原理理解	下一篇：WAS集群系列（2）：数据库连接低..