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'
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),其中红色字体与Oracle用户保持一致即可