生成控制文件

準備工作：

SQL>set colsep ' ';　　　　//-域輸出分隔符SQL>set echo off;　　　　 //顯示start啟動的腳本中的每個sql命令，缺省為onSQL> set echo on//設置運行命令是是否顯示語句SQL> set feedback on;//設置顯示“已選擇XX行”SQL>set feedback off;//回顯本次sql命令處理的記錄條數，缺省為onSQL>set heading off;　　 //輸出域標題，缺省為onSQL>set pagesize 0;　　 //輸出每頁行數，缺省為24,為了避免分頁，可設定為0。SQL>set linesize 80;　　 //輸出一行字符個數，缺省為80SQL>set numwidth 12;　 //輸出number類型域長度，缺省為10SQL>set termout off;　　 //顯示腳本中的命令的執行結果，缺省為onSQL>set trimout on;　　　//去除標準輸出每行的拖尾空格，缺省為offSQL>set trimspool on;　　//去除重定向(spool)輸出每行的拖尾空格，缺省為offSQL>set serveroutput on; //設置允許顯示輸出類似dbms_output

1、spool control.all;

select ctl_name from (

select 'spool ctl/'||lower(table_name)||'.ctl' ctl_name,table_name, 0 cid from user_tables a

union all

select 'select ''LOAD DATA TRUNCATE into table '||table_name,table_name,0.1 cid from user_tables a

union all

select 'FIELDS TERMINATED BY ?x'||chr(39)||'03'||chr(39)||' OPTIONALLY ENCLOSED BY '||chr(39)||'"'||chr(39),table_name, 0.2 cid from user_tables a

union all

select 'TRAILING NULLCOLS',table_name,0.3 cid from user_tables a

union all

select '(' ,table_name,0.4 cid from user_tables a

union all

select b.tab_column_ora,a.table_name,0.5 cid from user_tables a,tab_column_union b where a.table_name=b.TABLE_NAME

union all

select ') '' from dual;' ,table_name,0.6 cid from user_tables a

union all

select 'spool off',table_name,0.7 cid from user_tables a

) aa order by table_name,cid;

spool off;

-----------生成一個新表tab_column_union，tab_column_ora字段對時間類型的col字段進行處理，用gy_etl_download存儲過程處理。cid 偽列控制輸出順序。

-----------chr(39)是'的意思，兩個之間的部分有啥顯示啥。

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

備注：生成tab_column_union的存儲過程 。

備注：

CREATE OR REPLACE PROCEDURE GY_ETL_DOWNLOAD as

n_count integer;

v_column_ora varchar2(4000);

begin

update tab_column_union set tab_column_db2=null;

for c1 in (select column_name,a.table_name,column_id,data_type from user_tables a,user_tab_columns b where a.table_name=b.TABLE_NAME order by a.table_name,column_id)

loop

n_count:=0;

v_column_ora:=null;

select count(*) into n_count from tab_column_ex where table_name=c1.table_name and tab_column=c1.column_name;

if n_count> 0 then

select 'to_date'||'('||c1.column_name||','||chr(39)||'yyyy-mm-dd-hh24.mi.ss'||chr(39)||')' into v_column_ora from dual;

update tab_column_union set tab_column_ora=tab_column_ora||v_column_ora||',' where table_name=c1.table_name;

else

if c1.data_type='DATE' then

select c1.column_name||'"to_date(:'||c1.column_name||','||chr(39)||'yyyymmdd'||chr(39)||')"' into v_column_ora from dual;

update tab_column_union set tab_column_ora=tab_column_ora||v_column_ora||',' where table_name=c1.table_name;

else

update tab_column_union set tab_column_ora=tab_column_ora||c1.column_name||',' where table_name=c1.table_name;

end if;

end if;

END LOOP;

update tab_column_union t set tab_column_db2=substr(t.tab_column_db2,1,length(t.tab_column_db2)-1);

update tab_column_union t set tab_column_ora=substr(t.tab_column_ora,1,length(t.tab_column_ora)-1);

COMMIT;

end GY_ETL_DOWNLOAD;

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

2、在sqlplus里運行control.all腳本

SQL>@control.all

批量生成控制文件

3、執行命令腳本,腳本保存為impdata.sh

#!/bin/sh

#獲取當前目錄

currDir=`dirname $0`

cd $currDir

currDir=`pwd`

#數據庫連接串

dbConnStr=joey/jy_123orcl11g

#數據文件保存目錄

dataDir=$currDir/data

#控制文件

controlFile=$currDir/ctl/

#設置環境變量

#ORACLE_HOME=/opt/oracle/product/10201

#PATH=$PATH:$ORACLE_HOME/bin

#日志根目錄

baseLogDir=$currDir/log

#插入失敗數據的記錄的目錄

badLogDir=$baseLogDir/bad

#執行日志目錄 ?(導入日志)

impLogDir=$baseLogDir/imp

#命令執行日志目錄 (錯誤記錄)

cmdLogDir=$baseLogDir/cmd

#創建日志目錄

mkdir -p $badLogDir 2> /dev/null

mkdir -p $impLogDir 2> /dev/null

mkdir -p $cmdLogDir 2> /dev/null

for dataFile in `ls ${dataDir}/*.del`

do

logDateSuffix=`date "+%Y%m%d%H%M%S"`

sqlldr $dbConnStr \

silent=all errors=10000 \

data=$dataFile \

log=$impLogDir/`basename ${dataFile}`.$logDateSuffix.imp \

bad=$badLogDir/`basename ${dataFile}`.$logDateSuffix.bad \

control= $controlFile/`basename ${dataFile}`.ctl \

> /dev/null \

2>> $cmdLogDir/`basename ${dataFile}`.${logDateSuffix}.log

done

運行腳本 ?$./impdata.sh

總結

以上是生活随笔為你收集整理的oracle ctl文件7c1b,批量生成控制文件，用sqlldr自动导入多个表的数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。