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

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

 

注：这篇文档是本人上一篇同类文档的姊妹篇亦或是升级版吧
姊妹篇见我的博文： http://space.itpub.net/26143577/viewspace-704757

 

适用场景：

 存在含有日期（或固定格式）的文件名的多个文件，使用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系统或者数据仓库定期或不定期将平面文件数据导入数据库的重复性工作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26143577/viewspace-716662/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-716662/