您当前的位置:首页 > perl

DBMS_SCHEDULER执行PERL脚本加载数据

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

create_job参数:ewvlinux系统宝典

AttributeDescription

job_nameewvlinux系统宝典

Name of the jobewvlinux系统宝典

job_classewvlinux系统宝典

Name of the job classewvlinux系统宝典

job_styleewvlinux系统宝典

Style of the job:ewvlinux系统宝典

  • REGULARewvlinux系统宝典

  • LIGHTWEIGHTewvlinux系统宝典

program_nameewvlinux系统宝典

Name of the program that the job runsewvlinux系统宝典

job_actionewvlinux系统宝典

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.ewvlinux系统宝典

job_typeewvlinux系统宝典

Job action type ('PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE', or 'CHAIN')ewvlinux系统宝典

schedule_nameewvlinux系统宝典

Name of the schedule that specifies when the job has to executeewvlinux系统宝典

repeat_intervalewvlinux系统宝典

Inline time-based scheduleewvlinux系统宝典

schedule_limitewvlinux系统宝典

Maximum delay time between scheduled and actual job start before a job run is canceledewvlinux系统宝典

start_dateewvlinux系统宝典

Start date and time of the jobewvlinux系统宝典

end_dateewvlinux系统宝典

End date and time of the jobewvlinux系统宝典

event_conditionewvlinux系统宝典

Event condition for event-based jobsewvlinux系统宝典

queue_specewvlinux系统宝典

File watcher name or queue specification for event-based jobsewvlinux系统宝典

number_of_argumentsewvlinux系统宝典

Number of job argumentsewvlinux系统宝典

argumentsewvlinux系统宝典

Array of job argumentsewvlinux系统宝典

job priorityewvlinux系统宝典

Job priorityewvlinux系统宝典

job_weightewvlinux系统宝典

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

Weight of the job for parallel execution.ewvlinux系统宝典

max_run_durationewvlinux系统宝典

Maximum run duration of the jobewvlinux系统宝典

max_runsewvlinux系统宝典

Maximum number of runs before the job is marked as completedewvlinux系统宝典

max_failuresewvlinux系统宝典

Maximum number of failures tolerated before the job is marked as brokenewvlinux系统宝典

logging_levelewvlinux系统宝典

Job logging levelewvlinux系统宝典

restartableewvlinux系统宝典

Indicates whether the job is restartable (TRUE) or not (FALSE)ewvlinux系统宝典

stop_on_window_exitewvlinux系统宝典

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.ewvlinux系统宝典

raise_eventsewvlinux系统宝典

State changes that raise eventsewvlinux系统宝典

commentsewvlinux系统宝典

Comments on the jobewvlinux系统宝典

auto_dropewvlinux系统宝典

If TRUE (the default), indicates that the job should be dropped once completedewvlinux系统宝典

enabledewvlinux系统宝典

Indicates whether the job should be enabled immediately after creating it (TRUE) or not (FALSE)ewvlinux系统宝典

follow_default_timezoneewvlinux系统宝典

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.ewvlinux系统宝典

parallel_instancesewvlinux系统宝典

For event-based jobs only.ewvlinux系统宝典

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.ewvlinux系统宝典

If FALSE, then an event is discarded if it is raised while the job that handles it is already running,ewvlinux系统宝典

aq_jobewvlinux系统宝典

For internal use onlyewvlinux系统宝典

instance_idewvlinux系统宝典

The instance ID of the instance that the job must run onewvlinux系统宝典

credential_nameewvlinux系统宝典

The credential to use for a single destination or the default credential for a group of destinationsewvlinux系统宝典

destinationewvlinux系统宝典

The name of a single external destination or database destination, or a group name of type external destination or database destinationewvlinux系统宝典

database_roleewvlinux系统宝典

In an Oracle Data Guard environment, the database role ('PRIMARY' or 'LOGICALSTANDBY') for which the job runsewvlinux系统宝典

allow_runs_in_restricted_modeewvlinux系统宝典

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 modeewvlinux系统宝典

SET_JOB_ARGUMENT_VALUE参数:ewvlinux系统宝典

ParameterDescription

job_nameewvlinux系统宝典

The name of the job to be alteredewvlinux系统宝典

argument_nameewvlinux系统宝典

The name of the program argument being setewvlinux系统宝典

argument_positionewvlinux系统宝典

The position of the program argument being setewvlinux系统宝典

argument_valueewvlinux系统宝典

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

RUN_JOB参数:ewvlinux系统宝典

 ewvlinux系统宝典

ParameterDescription

job_nameewvlinux系统宝典

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.ewvlinux系统宝典

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

use_current_sessionewvlinux系统宝典

This specifies whether or not the job run should occur in the same session that the procedure was invoked from.ewvlinux系统宝典

When use_current_session is set to TRUE:ewvlinux系统宝典

  • 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.ewvlinux系统宝典

  • You can test a job and see any possible errors on the command line.ewvlinux系统宝典

  • run_count, last_start_date, last_run_duration, andfailure_count are not updated.ewvlinux系统宝典

  • RUN_JOB can be run in parallel with a regularly scheduled job run.ewvlinux系统宝典

When use_current_session is set to FALSE:ewvlinux系统宝典

  • The job runs as the user who is the job owner.ewvlinux系统宝典

  • You need to check the job log to find error information.ewvlinux系统宝典

  • run_count, last_start_date, last_run_duration, andfailure_count are updated.ewvlinux系统宝典

  • RUN_JOB fails if a regularly scheduled job is running.ewvlinux系统宝典

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 FALSEewvlinux系统宝典

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

 ewvlinux系统宝典

  • 'PLSQL_BLOCK'ewvlinux系统宝典

    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.ewvlinux系统宝典

  • 'STORED_PROCEDURE'ewvlinux系统宝典

    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.ewvlinux系统宝典

  • 'EXECUTABLE'ewvlinux系统宝典

    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.ewvlinux系统宝典

  • 'CHAIN'ewvlinux系统宝典

    This specifies that the job is a chain. Arguments are not supported for a chain, so number_of_arguments must be 0.ewvlinux系统宝典

2.由于用到dbms_scheduler包创建'EXECUTABLE'类型的job,需要对操作系统用户及数据库用户配置,以ETL(操作系统用户),ETL_TEST(数据库用户)为例进行配置,实验环境为5.5+Oracle11G(11.2.3)+Perl(5.8.8)

 ewvlinux系统宝典

a.创建操作系统用户ewvlinux系统宝典

[root@ETL ~]# useradd -d /home/etl/ -m etlewvlinux系统宝典
[root@ETL ~]# passwd etlewvlinux系统宝典
Changing password for user etl.ewvlinux系统宝典
New UNIX password: ewvlinux系统宝典
BAD PASSWORD: it is based on a dictionary wordewvlinux系统宝典

Retype new UNIX password: ewvlinux系统宝典

passwd: all authentication tokens updated successfully.ewvlinux系统宝典
注明:在linux系统中如果没有指定创建用户的组,系统会默认创建一个与用户名一致的用户组ewvlinux系统宝典

b.配置用户ETL的环境变量(/home/etl/.bash_profile),其中红色字体与Oracle用户保持一致即可ewvlinux系统宝典

# .bash_profileewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
# Get the aliases and functionsewvlinux系统宝典
if [ -f ~/.bashrc ]; thenewvlinux系统宝典
        . ~/.bashrcewvlinux系统宝典
fiewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
# User specific environment and startup programsewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
PATH=$PATH:$HOME/binewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
export PATHewvlinux系统宝典
export ORACLE_BASE=/u01/app/oracle  ewvlinux系统宝典
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1ewvlinux系统宝典
export ORACLE_SID=ETL ewvlinux系统宝典
export ORACLE_TERM=xterm  ewvlinux系统宝典
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" ewvlinux系统宝典
export ORA_NLS33=$ORACLE_HOME/common/nls/admin/data ewvlinux系统宝典
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib ewvlinux系统宝典
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib ewvlinux系统宝典
export LD_LIBRARY_PATH  ewvlinux系统宝典
export PATH=$PATH:$ORACLE_HOME/binewvlinux系统宝典

# .bash_profileewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
# Get the aliases and functionsewvlinux系统宝典
if [ -f ~/.bashrc ]; thenewvlinux系统宝典
        . ~/.bashrcewvlinux系统宝典
fiewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
# User specific environment and startup programsewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
PATH=$PATH:$HOME/binewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
export PATHewvlinux系统宝典
export ORACLE_BASE=/u01/app/oracle  ewvlinux系统宝典
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1ewvlinux系统宝典
export ORACLE_SID=ETL ewvlinux系统宝典
export ORACLE_TERM=xterm  ewvlinux系统宝典
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK" ewvlinux系统宝典
export ORA_NLS33=$ORACLE_HOME/common/nls/admin/data ewvlinux系统宝典
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib ewvlinux系统宝典
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib ewvlinux系统宝典
export LD_LIBRARY_PATH  ewvlinux系统宝典
export PATH=$PATH:$ORACLE_HOME/bin
ewvlinux系统宝典

 ewvlinux系统宝典

PATH=$PATH:$HOME/binewvlinux系统宝典

 ewvlinux系统宝典

c.配置脚本及数据文件相关路径ewvlinux系统宝典

[root@ETL /]# mkdir /ETLewvlinux系统宝典
[root@ETL /]# cd ETLewvlinux系统宝典
[root@ETL ETL]# mkdir badewvlinux系统宝典
[root@ETL ETL]# mkdir logewvlinux系统宝典
[root@ETL ETL]# mkdir loaderewvlinux系统宝典
[root@ETL ETL]# mkdir controlewvlinux系统宝典
[root@ETL ETL]# mkdir dataewvlinux系统宝典
[root@ETL ETL]# mkdir backupewvlinux系统宝典
[root@ETL ETL]# mkdir shewvlinux系统宝典
[root@ETL ETL]# mkdir perlewvlinux系统宝典

[root@ETL ETL]# cd ..ewvlinux系统宝典
[root@ETL /]# chown -R etl:etl /ETLewvlinux系统宝典
[root@ETL /]# chmod -R 777 /ETLewvlinux系统宝典

(目录说明:bad(sqlldr加载数据文件被拒的记录),log(sqlldr加载数据文件日志),loader(加载数据文件的perl脚本),control(sqlldr加载数据文件所用到的控制文件),data(sqlldr加载的数据文件,backup(数据文件的备份目录),sh(shell脚本目录),per(perl脚本目录).ewvlinux系统宝典

d.因为此次实验是用ETL_TEST(数据库用户)调用dbms_schduler包以ETL用户身份加载数据(sqlldr加载),以下为执行'EXECUTABLE'的job相关配置 ewvlinux系统宝典

      1.查看$ORACLE_HOME/rdbms/admin/externaljob.ora 权限ewvlinux系统宝典

[root@ETL ~]# su - oracleewvlinux系统宝典
[oracle@ETL ~]$ cd $ORACLE_HOMEewvlinux系统宝典
[oracle@ETL dbhome_1]$ pwdewvlinux系统宝典
/u01/app/oracle/product/11.2.0/dbhome_1ewvlinux系统宝典
[oracle@ETL dbhome_1]$ exitewvlinux系统宝典
logoutewvlinux系统宝典
[root@ETL ~]# cd /u01/app/oracle/product/11.2.0/dbhome_1ewvlinux系统宝典
[root@ETL dbhome_1]# cd rdbmsewvlinux系统宝典
[root@ETL rdbms]# cd adminewvlinux系统宝典
[root@ETL admin]# ls -al|grep externaljob.oraewvlinux系统宝典
-rw-r-----  1 root   oinstall    1536 Jan 30 13:28 externaljob.oraewvlinux系统宝典

(其中文件权限必须和上面一致)ewvlinux系统宝典

    2.配置$ORACLE_HOME/rdbms/admin/externaljob.ora,将run_user=etl run_group=etl 具体如下:ewvlinux系统宝典

[root@ETL admin]# vi externaljob.oraewvlinux系统宝典
# $Header: externaljob.ora 16-dec-2005.20:47:13 rramkiss Exp $ewvlinux系统宝典
#ewvlinux系统宝典
# Copyright (c) 2005, Oracle. All rights reserved.ewvlinux系统宝典
# NAMEewvlinux系统宝典
#   externaljob.oraewvlinux系统宝典
# FUNCTIONewvlinux系统宝典
#   This configuration file is used by dbms_scheduler when executing externalewvlinux系统宝典
#   (operating system) jobs. It contains the user and group to run externalewvlinux系统宝典
#   jobs as. It must only be writable by the owner and must be owned by root.ewvlinux系统宝典
#   If extjob is not setuid then the only allowable run_userewvlinux系统宝典
#   is the user Oracle runs as and the only allowable run_group is the groupewvlinux系统宝典
#   Oracle runs as.ewvlinux系统宝典
#ewvlinux系统宝典
# NOTESewvlinux系统宝典
#   For Porters: The user and group specified here should be a lowly privilegedewvlinux系统宝典
#                user and group for your platform. For Linux this is nobodyewvlinux系统宝典
#                and nobody.ewvlinux系统宝典
# MODIFIEDewvlinux系统宝典
#     rramkiss   12/09/05 -  Creationewvlinux系统宝典
#ewvlinux系统宝典
##############################################################################ewvlinux系统宝典
# External job execution configuration file externaljob.oraewvlinux系统宝典
#ewvlinux系统宝典
# This file is provided by Oracle Corporation to help you customizeewvlinux系统宝典
# your RDBMS installation for your site.  Important system parametersewvlinux系统宝典
# are discussed, and default settings given.ewvlinux系统宝典
#ewvlinux系统宝典
# This configuration file is used by dbms_scheduler when executing externalewvlinux系统宝典
# (operating system) jobs. It contains the user and group to run externalewvlinux系统宝典
# jobs as. It must only be writable by the owner and must be owned by root.ewvlinux系统宝典
# If extjob is not setuid then the only allowable run_userewvlinux系统宝典
# is the user Oracle runs as and the only allowable run_group is the groupewvlinux系统宝典
# Oracle runs as.ewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
run_user =etl    ewvlinux系统宝典
run_group =etl    ewvlinux系统宝典

    3.查看$ORACLE_HOME/bin/extjob文件权限ewvlinux系统宝典

[root@ETL admin]# su - oracleewvlinux系统宝典
[oracle@ETL ~]$ cd $ORACLE_HOMEewvlinux系统宝典
[oracle@ETL dbhome_1]$ pwdewvlinux系统宝典
/u01/app/oracle/product/11.2.0/dbhome_1ewvlinux系统宝典
[oracle@ETL dbhome_1]$ exitewvlinux系统宝典
logoutewvlinux系统宝典
[root@ETL admin]# cd /u01/app/oracle/product/11.2.0/dbhome_1ewvlinux系统宝典
[root@ETL dbhome_1]# ls -al|grep extjobewvlinux系统宝典
[root@ETL dbhome_1]# cd binewvlinux系统宝典
[root@ETL bin]# ls -al|grep extjobewvlinux系统宝典
-rwsr-x---  1 root   oinstall   1249595 Jan 18 00:53 extjobewvlinux系统宝典
-rwx------  1 oracle oinstall   1249595 Jan 18 00:53 extjoboewvlinux系统宝典
-rwxr-xr-x  1 oracle oinstall   1249958 Sep 17  2011 extjobOewvlinux系统宝典
-rwxr-xr-x  1 oracle oinstall   1249958 Sep 17  2011 extjoboOewvlinux系统宝典

(注明:extjob文件权限必须与上面保持一致)ewvlinux系统宝典

e.创建ETL_TEST(数据库用户),并给相应权限ewvlinux系统宝典

  1.创建 ETL_TEST用户ewvlinux系统宝典

create user etl_test identified by etl_testewvlinux系统宝典
default tablespace usersewvlinux系统宝典
temporary tablespace temp;ewvlinux系统宝典

  2.赋于相关的系统和对象权限ewvlinux系统宝典

grant connect, resource to etl_test;ewvlinux系统宝典
grant select on sys.v_$session to etl_test;ewvlinux系统宝典
grant select on sys.v_$process to etl_test;ewvlinux系统宝典
grant create job to etl_test;ewvlinux系统宝典
grant create any job to etl_test;ewvlinux系统宝典
grant create external job to etl_test;ewvlinux系统宝典
grant MANAGE SCHEDULER to etl_test;ewvlinux系统宝典
grant alter system to etl_test;ewvlinux系统宝典
grant execute on DBMS_LOCK to etl_test;ewvlinux系统宝典
grant execute on DBMS_PIPE to etl_test;ewvlinux系统宝典
grant execute on UTL_FILE to etl_test;ewvlinux系统宝典
grant execute on DBMS_SCHEDULER to etl_test;ewvlinux系统宝典
grant all on DBMS_SCHEDULER to etl_test;ewvlinux系统宝典
grant execute on DBMS_CRYPTO to etl_test;ewvlinux系统宝典
grant create any directory to etl_test;ewvlinux系统宝典
grant debug any procedure, debug connect session to etl_test; ewvlinux系统宝典
grant select on sys.dba_free_space to etl_test; ewvlinux系统宝典
grant select on sys.dba_data_files to etl_test;ewvlinux系统宝典

  3.创建Oracle的Directory并赋权ewvlinux系统宝典

create or replace directory RWA_FILE_DATA as '/ETL/data';ewvlinux系统宝典
create or replace directory RWA_FILE_BAD as '/ETL/bad';ewvlinux系统宝典
create or replace directory RWA_FILE_LOG as '/ETL/log';ewvlinux系统宝典
create or replace directory RWA_FILE_CONTROL as '/ETL/control';ewvlinux系统宝典
create or replace directory RWA_FILE_LOADER as '/ETL/loader';ewvlinux系统宝典
create or replace directory RWA_FILE_SH as '/ETL/sh';ewvlinux系统宝典
create or replace directory RWA_FILE_BACKUP as '/ETL/backup';ewvlinux系统宝典
create or replace directory RWA_FILE_PERL as '/ETL/perl';ewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
grant read, write on directory RWA_FILE_DATA to etl_test;ewvlinux系统宝典
grant read, write on directory RWA_FILE_PERL to etl_test;ewvlinux系统宝典
grant read, write on directory RWA_FILE_BAD to etl_test;ewvlinux系统宝典
grant read, write on directory RWA_FILE_LOG to etl_test;ewvlinux系统宝典
grant read, write on directory RWA_FILE_CONTROL to etl_test;ewvlinux系统宝典
grant read, write on directory RWA_FILE_LOADER to etl_test;ewvlinux系统宝典
grant read, write on directory RWA_FILE_SH to etl_test;ewvlinux系统宝典
grant read, write on directory RWA_FILE_BACKUP  to etl_test;ewvlinux系统宝典

f.加载数据文件ewvlinux系统宝典

  1.加载数据的表ewvlinux系统宝典

create table F_MUREX_GLewvlinux系统宝典
(ewvlinux系统宝典
  data_dt  DATE,ewvlinux系统宝典
  areano   VARCHAR2(10),ewvlinux系统宝典
  currency VARCHAR2(10),ewvlinux系统宝典
  apcode   VARCHAR2(20),ewvlinux系统宝典
  orgcde   VARCHAR2(20),ewvlinux系统宝典
  damount  NUMBER,ewvlinux系统宝典
  camount  NUMBER,ewvlinux系统宝典
  remark   VARCHAR2(1000)ewvlinux系统宝典
);ewvlinux系统宝典

  2.加载数据的控制文件,数据文件,shell脚本,perl脚本如下ewvlinux系统宝典

    a.RWA_EDW_PLEDGE_IMPAWN_INFO.ctl -- sqlldr控制文件 目录:/ETL/controlewvlinux系统宝典

[etl@ETL control]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.ctlewvlinux系统宝典
load dataewvlinux系统宝典
TRUNCATE into table F_MUREX_GLewvlinux系统宝典
fields terminated by X'01' ewvlinux系统宝典
trailing nullcolsewvlinux系统宝典
(DATA_DT DATE'yyyy-mm-dd',AREANO,CURRENCY,APCODE,ORGCDE,DAMOUNT,CAMOUNT,REMARK)ewvlinux系统宝典

   b.RWA_EDW_PLEDGE_IMPAWN_INFO.sh -- 加载数据的shell文件 目录:/ETL/loaderewvlinux系统宝典

[etl@ETL loader]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.shewvlinux系统宝典
#!/bin/shewvlinux系统宝典
. /home/etl/.bash_profileewvlinux系统宝典
vOraPwd=$1ewvlinux系统宝典
sqlldr userid=etl_test/$vOraPwd@ETL control=/ETL/control/RWA_EDW_PLEDGE_IMPAWN_INFO.ctl data=/ETL/data/RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt log=/ETL/log/RWAewvlinux系统宝典
_EDW_PLEDGE_IMPAWN_INFO.log bad=/ETL/bad/RWA_EDW_PLEDGE_IMPAWN_INFO.badewvlinux系统宝典

  c.RWA_EDW_PLEDGE_IMPAWN_INFO.pl  -- 调用加载数据文件的shell脚本(RWA_EDW_PLEDGE_IMPAWN_INFO.sh)ewvlinux系统宝典

#! /usr/bin/perlewvlinux系统宝典
########################################################ewvlinux系统宝典
# @name :RWA_EDW_PLEDGE_IMPAWN_INFO.plewvlinux系统宝典
# @parameter : db user pasaword ewvlinux系统宝典
# @description : run RWA_EDW_PLEDGE_IMPAWN_INFO.sh and load data to table F_MUREX_GLewvlinux系统宝典
#                 ewvlinux系统宝典
# @create_date :2015-02-09ewvlinux系统宝典
# @author :Tuxewvlinux系统宝典
# @version :1.0.0ewvlinux系统宝典
# @source :ewvlinux系统宝典
# @target :ewvlinux系统宝典
# @modify :ewvlinux系统宝典
# @copyright :ewvlinux系统宝典
####################################################################ewvlinux系统宝典
use strict;ewvlinux系统宝典
my $passwd;ewvlinux系统宝典
my $clm_shell = '/ETL/loader/RWA_EDW_PLEDGE_IMPAWN_INFO.sh';ewvlinux系统宝典
$passwd = $ARGV[0];ewvlinux系统宝典
# run shell script ewvlinux系统宝典
eval {ewvlinux系统宝典
    system("sh $clm_shell $passwd");ewvlinux系统宝典
};ewvlinux系统宝典
ewvlinux系统宝典
ewvlinux系统宝典
if ($@ ne '') {ewvlinux系统宝典
    die "execute sqlldr  script failed/n";ewvlinux系统宝典
}ewvlinux系统宝典
else ewvlinux系统宝典
{ewvlinux系统宝典
print  "the sqlldr script run sucessessfull !!/n";
ewvlinux系统宝典

}ewvlinux系统宝典
ewvlinux系统宝典

d.RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt  -- 数据文件ewvlinux系统宝典

[etl@ETL data]$ more RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txtewvlinux系统宝典
2014-06-30^^^^ewvlinux系统宝典
2014-06-30   00350AED0232    6114                  0.000        1000000.000                       ewvlinux系统宝典
2014-06-30   00350AUD0148    6107            4538300.000              0.000                       ewvlinux系统宝典
2014-06-30   00350AUD0110    6107            1526300.000              0.000                       ewvlinux系统宝典
2014-06-30   00350AUD0971    6107            8006100.000              0.000                       ewvlinux系统宝典
2014-06-30   00350AUD0158    6107                154.430              0.000                       ewvlinux系统宝典
2014-06-30   00350AUD5497    6108                  0.000          15200.000                       ewvlinux系统宝典
2014-06-30   00350AUD0155    6108            1000000.000              0.000                       ewvlinux系统宝典
2014-06-30   00350CAD0239    6107                  0.000         950000.000                       ewvlinux系统宝典
2014-06-30   00350CAD0247    6107                  0.000         950000.000                       ewvlinux系统宝典
2014-06-30   00350CAD9317    6107             262222.000              0.000                       ewvlinux系统宝典
2014-06-30   00350CAD0123    6114            1000000.000              0.000                       ewvlinux系统宝典
2014-06-30   00350CHF0971    6107            2383200.000              0.000                       ewvlinux系统宝典
2014-06-30   00036CNY9867    6118             572590.240              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY7066    6118            9000000.000              0.000                       ewvlinux系统宝典
2014-06-30   00036CNY7048    6118         7546536516.090              0.000                       ewvlinux系统宝典
2014-06-30   00036CNY6814    6118            1323765.700              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY5512    6107                  0.000     1089729877.740                       ewvlinux系统宝典
2014-06-30   00350CNY9861    6118             248471.230              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY9887    6118               2666.660              0.000                       ewvlinux系统宝典
2014-06-30   00036CNY5433    6104                  0.000         130676.080                       ewvlinux系统宝典
2014-06-30   00350CNY5453    6107          500495181.380              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY5562    6107                  0.000       50571245.930                       ewvlinux系统宝典
2014-06-30   00350CNY7591    6124           69524032.280              0.000                       ewvlinux系统宝典
2014-06-30   00036CNY9971    6104            4313539.170              0.000                       ewvlinux系统宝典
2014-06-30   00036CNY6025    6121                105.860              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY5149    6118                  0.000         108304.850                       ewvlinux系统宝典
2014-06-30   00350CNY5605    6110                  0.000        1790434.050                       ewvlinux系统宝典
2014-06-30   00350CNY7274    6110           78709183.050              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY7272    6110            1000000.000              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY9322    6107                  0.000      316363894.130                       ewvlinux系统宝典
2014-06-30   00350CNY8804    6120             328682.870              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY0961    6116             330410.960              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY0845    6116                  0.000        3041470.520                       ewvlinux系统宝典
2014-06-30   00350CNY0745    6116            3041470.520              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY0975    6116          200000000.000              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY8578    6110                  0.000       98844709.540                       ewvlinux系统宝典
2014-06-30   00350CNY7492    6110            3501396.240              0.000                       ewvlinux系统宝典
2014-06-30   00350CNY8635    6110           98844709.540              0.000                       ewvlinux系统宝典
2014-06-30   00350EUR9875    6112                  0.000        3000000.000 ewvlinux系统宝典
ewvlinux系统宝典

3.创建加载数据文件的EXCUTABLE类型job,执行并查看日志ewvlinux系统宝典

-- 创建executable jobewvlinux系统宝典
beginewvlinux系统宝典
       dbms_scheduler.create_job(ewvlinux系统宝典
       job_name => 'LF_PERL',ewvlinux系统宝典
       job_type => 'EXECUTABLE',ewvlinux系统宝典
       job_action => '/ETL/loader/RWA_EDW_PLEDGE_IMPAWN_INFO.pl',ewvlinux系统宝典
       start_date => systimestamp,ewvlinux系统宝典
       number_of_arguments => 1,ewvlinux系统宝典
     --  job_class       => 'no_logging_class',ewvlinux系统宝典
       auto_drop => true,ewvlinux系统宝典
       comments => 'LF_PERL');ewvlinux系统宝典
end;ewvlinux系统宝典
-- 传入参数ewvlinux系统宝典
begin ewvlinux系统宝典
    dbms_scheduler.set_job_argument_value(job_name => 'LF_PERL',ewvlinux系统宝典
                                          argument_position =>1 ,ewvlinux系统宝典
                                          argument_value =>'etl_test' );ewvlinux系统宝典
end;ewvlinux系统宝典
-- 执行ewvlinux系统宝典
begin ewvlinux系统宝典
   dbms_scheduler.run_job(job_name =>'LF_PERL');ewvlinux系统宝典
end;ewvlinux系统宝典
-- 删除jobewvlinux系统宝典
begin ewvlinux系统宝典
  dbms_scheduler.drop_job(job_name => 'LF_PERL');ewvlinux系统宝典
end;ewvlinux系统宝典
-- 清除job日志ewvlinux系统宝典
begin ewvlinux系统宝典
  dbms_scheduler.purge_log(job_name => 'LF_PERL');ewvlinux系统宝典
end;ewvlinux系统宝典
ewvlinux系统宝典

-- 查看JOBewvlinux系统宝典
select * from user_scheduler_jobs tewvlinux系统宝典
where job_name = 'LF_PERL'ewvlinux系统宝典
;ewvlinux系统宝典
-- 查看JOB执行情况ewvlinux系统宝典
select * from user_scheduler_job_run_details tewvlinux系统宝典
where job_name = 'LF_PERL'ewvlinux系统宝典
ewvlinux系统宝典

ewvlinux系统宝典

更多Oracle相关信息见 专题页面 ewvlinux系统宝典



沪ICP备10206494号-4