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系統或者資料倉儲定期或不定期將平面檔案資料匯入資料庫的重複性工作。