sqlldr

最近在處理一批文本數據，規定按照接口說明導入到ORACLE數據庫中，經過最近的研究，解決了一些問題，覺得我必要總結一下：

常規資料：

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

Dos 環境下使用SQl*Loader命令 加載

使用其它數據庫的數據轉移工具

Oracle 企業管理器中的數據加載功能

具體的技術實現

一、Dos 環境下加載

1、首先，服務器端的偵聽服務必須已經開啟。

測試方法：Dos 下輸入

C:/>sqlplus username/password@serviceName

2、然后使用 Oracle 的 sqlldr 命令進行數據的導入

前期條件

1） Oracle 數據庫端必須已經建好了需要導入的數據表的結構

2） 一個數據源文件 下面例子中為制表符分隔的文本文件 model.txt ,為Excel 表中導出的

3） 手工編輯一個XXX.CTL 的控制文件

4） 命令行加載數據

如下實例：

以下文件缺省放到C:/ 下，如果不是，就需要指明全路徑

1． 命令控制文件 input.ctl 內容

命令 
說明

load data 
1、控制文件標識

infile 'model.txt' 
2、要輸入的數據文件名為test.txt

append into table system.塔位屬性表 
3、向表test中追加記錄

fields terminated by X'09' 
4、指定分隔符，字段終止于X'09'，是一個制表符（TAB）

(編號,名稱,大小) 
5、定義列對應表中順序

控制文件中指定插入數據的方式關鍵字

insert，為缺省方式，在數據裝載開始時要求表為空

append，在表中追加新記錄

replace，刪除舊記錄，替換成新裝載的記錄

truncate，同上

在 Dos 窗口下使用 SQl*Loader 命令實現數據的導入

C:/>sqlldr userid=system/manager@ serviceName control=input.ctl

默認日志文件名為：input.log

默認壞記錄文件為：input.bad

二、使用其它數據庫轉移工具

以下以SQL Server 導入導出向導為例

1、在數據的導入導出向導中設置數據源服務器，實例中選擇數據源選擇SQL Server

2、然后指定要導入的Oracle 數據源

3、需要配置Oracle 的屬性信息

需要注意的是，登錄數據庫的用戶信息即為數據導入之后的方案名，即導入之后的SQL Server 中的表在Oracle 中標志名為 username.表名

以下按照提示即可，可以完全導入SQl Server 中的數據表和視圖，也可以使用查詢語句返回你要選擇的列或者行。

三、Oracle 企業管理器中的數據加載功能

登錄Oracle 的控制臺界面，針對單獨的數據表可以使用數據加載工具

中間需要指定控制文件等，同Dos 加載一致，不再重復

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

向Oracle中導入文本數據時使用的控制文件格式
 
無論是使用上一篇中的哪種方式都需要有一個控制文件，下面是控制文件（ctl文件）書寫的基本格式：
    
 命令
 說明
 
load data
 1、控制文件標識
 
infile 'testl.txt'　
 2、要輸入的數據文件名為test.txt，此時是要導入的數據文件同控制文件在同一路徑下，如果不在同一路徑下則需要寫完整路徑名
 
append into table 表名（可以是全名也可以是同義詞）
 3、向表test中追加記錄
 
fields terminated by X'09'
 4、指定分隔符，字段終止于X'09'   (很重要)，是一個制表符（TAB），如果用逗號分割就將X'09'替換為','
 
(編號,名稱,大小)
 5、定義列對應表中順序
 

  控制文件中指定插入數據的方式關鍵字

                           insert，為缺省方式，在數據裝載開始時要求表為空

                           append，在表中追加新記錄

                           replace，刪除舊記錄，替換成新裝載的記錄

                           truncate，同上

控制文件的示例：
load data
infile 'test.txt'
append into table test.test
fields terminated by X'09'
(test,test1,test2)

對有時間類型的數據導入的示例控制文件：
load data 
infile 'h:/TB_FACT_PHS_TICKET_DAY.txt'
Append into TABLE TB_FACT_PHS_TICKET_DAY
fields terminated by X'09'
(Time_Id            
,Region_Id         
,Cust_Type_Id      
,Prod_Type_Id      
,Acct_Item_Type_Id 
,Acct_Item_Type_Cd 
,Exchange97_Cd     
,Latn_Cd           
,Call_Duration     
,Access_In_Duration
,Income
,In_Date  Date "YYYY-MM-DD"
)

文件導入命令                    C:/>sqlldr userid=test/test@test control=test.ctl(此時控制文件test.ctl存在C:/路徑下)
在命令控制符下進入Oracle        C:/>sqlplus username/password@serviceName

oracle導入txt數據文件2008年07月30日 星期三 17:21把txt文件格式的數據文件導入oracle的方法是利用sqlloader工具。

第一步：把文本格式的數據文件放入C盤。如,test.txt
第二步：建立控制文件append.ctl。（名字可以隨便取,放C盤下）

append.ctl的內容如下：
　　load data　　　　　　　　　　   --1、控制文件標識 
   　infile   'test.txt'　　　　　　 --2、要輸入的數據文件名為test.txt 
   　append into table CTXSYS.test　　　--3、向CTXSYS表空間中的表test中追加記錄 
   　fields terminated by X'09' --4、字段終止于X'09'，是一個制表符
(id,username,password,sj)　　   -----定義列對應順序

   其中append為數據裝載方式，還有其他選項： 
   a、insert，為缺省方式，在數據裝載開始時要求表為空 
   b、append，在表中追加新記錄 
   c、replace，刪除舊記錄，替換成新裝載的記錄 
   d、truncate，同上

第三步：在命令提示符下輸入命令。
C:/>sqlldr userid=username/password

control=c:/append.ctl        數據庫中用名的用戶名和密碼
或者 C:/>sqlldr userid=system/manager@ serviceName control=input.ctl 

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

一些經常出現的問題：

1。關于日期格式的問題：

ctl基本寫法諸如：

load data
infile 'C:/TP_LOANCONTRACTSUM.txt'
insert into table TP_LOANCONTRACTSUM
fields terminated by '|!'
(
column01,
column02,
column03,
column04 "to_date(:column04,'''yyyy-mm-dd hh34:mi:ss''')",
column05 "to_date(:column05,'''yyyy-mm-dd hh34:mi:ss''')",
column06,
column07,
column08,
column09,
column10,
column11,
column12 "to_date(:column12,'''yyyy-mm-dd hh34:mi:ss''')",
column13
)
 

2。關于長字符串問題，CTL默認情況下是256位（或者256位左右），所以長字符串時會在log里報錯，提示所輸入的值超過最大長度，解決辦法，在ctl文件里再指定大小，注意個情況，不能寫VARCHAR只能寫CHAR，否則報錯，諸如：

load data
infile 'C:/TP_PLEDGECONTRACTINFO.txt'
insert into table TP_PLEDGECONTRACTINFO
fields terminated by '|!'
(
column01,
column02,
column03,
column04,
column05,
column06,
column07 "to_date(:column07,'''yyyy-mm-dd hh34:mi:ss''')",
column08,
column09,
column10 "to_date(:column10,'''yyyy-mm-dd hh34:mi:ss''')",
column11,
column12,
column13,
column14,
column15 "to_date(:column15,'''yyyy-mm-dd hh34:mi:ss''')",
column16,
column17,
column18 "to_date(:column18,'''yyyy-mm-dd hh34:mi:ss''')",
column19,
column20,
column21,
column22,
column23,
column24,
column25 "to_date(:column25,'''yyyy-mm-dd hh34:mi:ss''')",
column26 CHAR(500),
column27,
column28
)