shell脚本linux下sqlldr,sqlldr配合shell解决sqlldr 控制文件中引入变量的方案

sqlldr配合shell解决sqlldr 控制文件中引入变量的方案

适用场景：

存在含有日期(或固定格式)的文件名的多个文件，使用oracle sqlldr将文件本身内容及文件名相关信息导入oracle数据库表，即就是解决oracle sqlldr中使用外部变量这一问题。

(一)创建原始控制文件模板

[oracle@hostname sqlldr]$ pwd

/opt/mydir/sqlldr

[oracle@hostname sqlldr]$ cat ctl.ctl

load data

infile '/opt/mydir/sqlldr/file_temp/test.csv'

into table tablename append

fields terminated by X'09' TRAILING NULLCOLS(

TRADINGTIME,LASTPRICE,VOLUME,BIDPRICE1,BIDVOLUME1,BIDPRICE2,BIDVOLUME2,BIDPRICE3,BIDVOLUME3,ASKPRICE1,ASKVOLUME1,ASKPRICE2,ASKVOLUME2,ASKPRICE3,ASKVOLUME3,

TRADINGDATE "to_date('namedate','yyyy/mm/dd')"

)

注：其中namedate为文件名中的变量信息

(二)创建shell 文件

[oracle@hostname sqlldr]$ pwd

/opt/mydir/sqlldr

[oracle@hostname sqlldr]$ cat change.sh

#!/bin/bash

file_in=/opt/mydir/sqlldr/testsource

file_temp=/opt/mydir/sqlldr/file_temp

read -p "Enter user name:" name

stty -echo

read -p "Enter user password:" password

echo

stty echo

read -p "Enter tablename:" tablename

for file_a in ${file_in}/*; do

cp $file_a $file_temp/test.csv

filename=$(echo ${file_a##*/})

namedate=$(echo $file_a | awk -F[".""_"] '{ print $(NF-1) }')

cat /opt/mydir/sqlldr/ctl.ctl | sed "s/namedate/$namedate/g" | sed "s/tablename/$tablename/g" > newctl.ctl

sqlldr $name/$password control=/opt/mydir/sqlldr/newctl.ctl bad=/opt/mydir/sqlldr/bads/$filename.bad  log=/opt/mydir/sqlldr/logs/$filename.log  direct=true

rm -rf $file_temp/*

done

rm -rf /opt/mydir/sqlldr/testsource/*

(三)创建相关目录

[oracle@hostname sqlldr]$ ll

drwxr-xr-x. 2 oracle oinstall  4096  2月 17 10:00 bads

-rwxrwxrwx. 1 oracle oinstall   714  2月 17 09:44 change.sh

-rw-r--r--. 1 oracle oinstall   334  2月 16 16:21 ctl.ctl

drwxrwxrwx. 2 oracle oinstall  4096  2月 17 10:00 file_temp

drwxr-xr-x. 2 oracle oinstall  4096  2月 17 10:00 logs

drwxrwxrwx. 2 oracle oinstall 40960  2月 17 10:00 testsource

(四)开始使用

1、将原始文件使用类ftp软件传至相关目录，如下：

[oracle@hostname testsource]$ pwd

/opt/mydir/sqlldr/testsource

[oracle@hostname testsource]$ ll

总用量 2924

-rw-r--r--. 1 root root 639107  1月 13 18:11 agspot_tick20120113.TXT

-rw-r--r--. 1 root root 513171  1月 16 18:19 agspot_tick20120116.TXT

-rw-r--r--. 1 root root 562063  1月 17 17:37 agspot_tick20120117.TXT

2、在oracle库中根据文件特点建立相关表，如下：

SQL> desc agspot_tick_test

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

TRADINGTIME                                        VARCHAR2(8)

LASTPRICE                                          NUMBER(9,3)

VOLUME                                             NUMBER(9,3)

ASKPRICE3                                          NUMBER(9,3)

ASKPRICE2                                          NUMBER(9,3)

ASKPRICE1                                          NUMBER(9,3)

BIDPRICE1                                          NUMBER(9,3)

BIDPRICE2                                          NUMBER(9,3)

BIDPRICE3                                          NUMBER(9,3)

ASKVOLUME3                                         NUMBER(10)

ASKVOLUME2                                         NUMBER(10)

ASKVOLUME1                                         NUMBER(10)

BIDVOLUME1                                         NUMBER(10)

BIDVOLUME2                                         NUMBER(10)

BIDVOLUME3                                         NUMBER(10)

TRADINGDATE                                        DATE

3、运行shell脚本

[oracle@hostname sqlldr]$ pwd

/opt/mydir/sqlldr

[oracle@hostname sqlldr]$ sh change.sh

Enter user name:schema

Enter user password:

Enter tablename:agspot_tick_test

SQL*Loader: Release 11.2.0.1.0 - Production on 星期五 2月 17 10:31:12 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 23767.

SQL*Loader: Release 11.2.0.1.0 - Production on 星期五 2月 17 10:31:12 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 21284.

SQL*Loader: Release 11.2.0.1.0 - Production on 星期五 2月 17 10:31:13 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 19893.

注：脚本运行完后，会自动删除原始文件，可以在 bads和logs目录下查看oracle sqlldr相关日志信息。

4、检查表数据

SQL> select count(*) from agspot_tick_test;

COUNT(*)

----------

64941

至此，问题解决，可以用于类olap系统或者数据仓库定期或不定期将平面文件数据导入数据库的重复性工作。