oracle導成文字格式

在Oracle中你找不到來完成這個功能的工具. 我以前曾經用PowerBuilder的資料管道來完成這個任務, 後來在AskTom上看到有兩個工具, 其中一個是用SQL*Plus的SPOOL命令來做的, 另一個是用Pro*C程式來寫的. 用SQL*Plus的那種方法要比Pro*C的慢一些, 但是Pro*C因為和:

[@more@]

如何匯出Oracle中的資料到文字檔案?

在Oracle中你找不到來完成這個功能的工具. 我以前曾經用PowerBuilder的資料管道來完成這個任務, 後來在AskTom上看到有兩個工具, 其中一個是用SQL*Plus的SPOOL命令來做的, 另一個是用Pro*C程式來寫的. 用SQL*Plus的那種方法要比Pro*C的慢一些, 但是Pro*C因為和Oracle的版本結合太緊密, 不同的客戶端版本需要重新編譯, 因此在有不同版本的Oracle的環境下使用時不太方便.

幾個月前, 我正在學習使用Oracle客戶端的C程式介面(OCI)來編寫資料庫互動程式, 就用寫一個這樣的工具來作為練習題, 最後完成了任務, 並且在Linux/Unix下的不同的Oracle客戶端版本下也不需要重新編譯程式(在8i/9i/10g)的客戶端下試過了.

這個工具可以免費下載, 在下載的檔案中包括了Windows, Linux, Solaris下的可執行檔案. 來看一下這個工具的命令列選項:

Usage: ociuldr user=... query=... field=... record=... file=...
(@) Copyright Lou Fangxin 2004/2005, all rights reserved.
Notes:
-si = enable logon as SYSDBA
user =
sql = SQL file name, one sql per file, do not include ";"
query = select statement
field = seperator string between fields
record= seperator string between records
file = output file name(default: uldrdata.txt)
read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
sort = set SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE at session level (UNIT:MB)
hash = set HASH_AREA_SIZE at session level (UNIT:MB)
serial= set _serial_direct_read to TRUE at session level
trace = set event 10046 to given level at session level

for field and record, you can use '0x' to specify hex character code,
r=0x0d n=0x0a |=0x7c ,=0x2c t=0x09

其中read/sort/hash三個命令列選項是用來最佳化用的. 下面的例子用"#"來作為欄位間的分隔符, 用換行作為記錄的分隔符:

C:ociuldr query="select * from tab" field="|" file=anysql_tab.txt table=d_risk
3100 bytes allocated for column TNAME (1)
800 bytes allocated for column TABTYPE (2)
4100 bytes allocated for column CLUSTERID (3)

0 rows exported at 2006-08-03 10:54:38
12 rows exported at 2006-08-03 10:54:38

C:>type anysql_tab.txt
AULEXT#TABLE#
A_SEQ_SYN#SYNONYM#
A_V#VIEW#
OBJD_LIST#TABLE#
T#TABLE#
TRANSACTION_CACHE_IOT_0#TABLE#
T_HASH#TABLE#
T_LOB#TABLE#
T_LONG#TABLE#
T_LONGRAW#TABLE#
T_TRUNC2#TABLE#
T_TRUNC4#TABLE#

這個工具用100大小的陣列來從伺服器端取得記錄, 從兒保證了這個工具的良好效能. 現在的版本中還沒有自動生成用SQL Loader來裝載資料用的控制檔案, 下一次更新將包括這個功能, 在Linux/Unix下使用時需要設定三個環境變數:

使用ociuldr匯出date型別的資料

在使用ociuldr工具的時候，為了匯出date型別的欄位，最好使用to_char函式把date型別的欄位轉為你需要的欄位如('yyyy-mm

-dd hh24:mi:ss')。有這樣一個表結構:
Name Type Nullable Default Comments
----------- -------------- -------- ------- ---------------------
ID NUMBER Y
NAME VARCHAR2(20) Y
AGE NUMBER Y
DESCRIPTION NVARCHAR2(100) Y
BIRTHDAY DATE

有記錄:
1 小明 10 淘氣的孩子 1997-8-8 8:08:08
2 小孫 11 愛學習的孩子 1996-8-8 9:09:09

在使用ociuldr匯出資料的時候，最好寫成如下(這裡只關注query引數，其他的引數可以自己選擇設定)：
c:>ociuldr query="select id,name,age,description, to_char(birthday,'yyyy-mm-dd hh24:mi:ss')as birthday from tttt" field=# record=0x0a file=D:emp1.txt table=tttt

在d:emp1.txt中的內容為:
1#小明 #10#淘氣的孩子#1997-08-08 08:08:08
2#小孫#11#愛學習的孩子#1996-08-08 09:09:09

如果按照下面的方式匯出資料:
c:>ociuldr query="select * from tttt" field=# record=0x0a file=D:emp1.txt table=tttt

在d:emp1.txt中的內容為如下所示(只有年月日，沒有時分秒):
1#小明 #10#淘氣的孩子#08-8月 -97
2#小孫#11#愛學習的孩子#08-8月 -96

透過上面的分析，我們還可以知道在查詢date型別的欄位的時候，我們可以透過to_char的轉換，轉為任意格式的顯示格式。
select to_char(birthday,'hh24:mi') from scada.tttt;
08:08
09:09

必須注意到在ociuldr中，把date型別的欄位作為一個普通的String進行了處理。

使用sqlldr匯入資料

刪除tttt表中的記錄透過sqlldr把匯出的資料匯入到tttt表中去,注意必須是第一次透過呼叫to_char使用yyyy-mm-dd hh24:mi:ss格式匯出date型別記錄.

注意: 當使用ociuldr生成控制指令碼的時候，由於時間欄位使用了to_char函式轉換為了字元格式，因此在控制檔案中date型別的欄位變為了char欄位，必須手動改為date，並且定義自己需要的格式.

控制檔案tttt_sqlldr.ctl的內容是:
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'D:emp1.txt' "STR X'0a'"
INTO TABLE tttt
FIELDS TERMINATED BY X'23' TRAILING NULLCOLS
(
ID CHAR(40),
NAME CHAR(20),
AGE CHAR(40),
DESCRIPTION CHAR(200),
BIRTHDAY DATE "YYYY-MM-DD HH24:MI:SS"
)

在控制檯命令列下執行: c:>sqlldr control=d:tttt_sqlldr.ctl