sql loader的控制檔案可否使用變數variable嗎

前言

如何在sqlldr中的ctl檔案的infile裡 引入變數   折騰一晚上  實在沒招啊

測試明細

生成載入到ORACLE的原資料
[oracle@seconary zhaixunyang]$ pwd
/home/oracle/zhaixunyang


[oracle@seconary zhaixunyang]$ more data_source.txt 
1,2
2,3
3,4




建立用於載入資料的測試表
[oracle@seconary zhaixunyang]$ sqlplus scott/system


SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 14:20:05 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create table t_source(a int,b int);


Table created.


生成SQL LOADER載入資料的控制檔案
[oracle@seconary zhaixunyang]$ more insert_source.ctl 
load data
infile '/home/oracle/zhaixunyang/data_source.txt'
append into table t_source
fields terminated by ','
(a,b)


執行sql loader
[oracle@seconary zhaixunyang]$ sqlldr userid=scott/system control=insert_source.ctl  log=sqlloader.log


SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jun 11 14:23:57 2015


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


Commit point reached - logical record count 4




驗證SQLLOADER載入結果
[oracle@seconary zhaixunyang]$ sqlplus scott/system


SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 14:24:16 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select a,b from t_source;


         A          B
---------- ----------
         1          2
         2          3
         3          4




截斷測試表資料
[oracle@seconary zhaixunyang]$ sqlplus scott/system


SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 14:26:57 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> truncate table t_source;


Table truncated.         


複製原資料到ORACLE_HOME下
[oracle@seconary zhaixunyang]$ cp data_source.txt  $ORACLE_HOME
[oracle@seconary zhaixunyang]$ 




調整SQL LOADER 控制檔案
[oracle@seconary zhaixunyang]$ more insert_source.ctl 
load data
infile '$ORACLE_HOME/data_source.txt'
append into table t_source
fields terminated by ','
(a,b)




執行sql loader


[oracle@seconary zhaixunyang]$ sqlldr userid=scott/system control=insert_source.ctl  log=sqlloader.log


SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jun 11 14:31:43 2015


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


Commit point reached - logical record count 4


[oracle@seconary zhaixunyang]$ sqlplus scott/system


SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 14:32:23 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select a,b from t_source;


         A          B
---------- ----------
         1          2
         2          3
         3          4


         

個人簡介