一.外部表特性

數據文件位于操作系統之外,并且具有一定的格式分割的文本文件或其他類型文件.ORACLE的外部表通過SQL的形式訪問數據文件中的數據,數據并不需要加載到數據庫中且數據是可讀的,所以不用DML操作,創建索引

二.創建外部表的步驟

? A.創建目錄對象,這一點限制數據必須的SERVER端
? B.數據文件的準備,數據文件要求為操作系統之外且固定格式,不能有標題
? C.創建外部表的字段如有特殊字段需用雙引號 ”SYS_ID#”
? D.刪除外部表及目錄,應先刪除表后再刪除目錄

三.創建外部表實列

創建目錄:
CREATE OR REPLACE DIRECTORY SQLDR AS 'D:\ORACLE\ORADATA\DATA';

創建外部表:(通過SQLLDR方式的日志產生)
-- CREATE TABLE
CREATE TABLE SYS_SQLLDR_X_EXT_MUREX_GL_TEMP
(
? AREANO?? VARCHAR2(20),
? CURRENCY VARCHAR2(20),
? APCODE?? VARCHAR2(20),
? ORGCDE?? VARCHAR2(20),
? DAMOUNT? VARCHAR2(200),
? CAMOUNT? VARCHAR2(200),
? REMAKR?? VARCHAR2(200)
)
ORGANIZATION EXTERNAL
(
? TYPE ORACLE_LOADER
? DEFAULT DIRECTORY SQLDR
? ACCESS PARAMETERS
? (
??? RECORDS DELIMITED BY 0X'0A'
??????? BADFILE 'SQLDR':'MUREX_GLRCN_INIT_BADFILE.DAT'
??????? DISCARDFILE 'SQLDR':'MUREX_GLRCN_INIT_DISFILE.DAT'
??????? LOGFILE 'MUREX_GL_TEMP.LOG_XT'
??????? READSIZE 1048576
??????? FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY ' ' LDRTRIM
??????? REJECT ROWS WITH ALL NULL FIELDS
??????? (
????????? "AREANO" CHAR(255),
????????? "CURRENCY" CHAR(255),
????????? "APCODE" CHAR(255),
????????? "ORGCDE" CHAR(255),
????????? "DAMOUNT" CHAR(255),
????????? "CAMOUNT" CHAR(255),
????????? "REMAKR" CHAR(255),
??????? )
? )
? LOCATION (SQLDR:'MUREX_GLRCN_INIT_20190831.DAT')
)
REJECT LIMIT UNLIMITED;

四.SQLLDR產生外部表的DDL語句

在命令運行SQLLDR(控制文件事先要準備好):
A.用SQLLDR生成LOG文件
SQLLDR USER_NAME/PASSWORD@ORADB CONTROL=CONTROL_FILE.CTL EXTERNAL_TABLE=GERNERATE_ONLY;
NOT_USED：默認值。
EXECUTE：這個值說明SQLLDR不會生成并執行一個SQLINSERT語句；而是會創建一個外部表，且使用一個批量SQL語句來加載。
GENERATE_ONLY：使SQLLDR并不具體加載任何數據，而只是會生成所執行的SQL DDL和DML語句，并放到它創建的日志文件中。
注：DIRECT=TRUE覆蓋EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，則會加載數據，而不會生成外部表。

D:\ORACLE\ORADATA\DATA>SQLLDR BOC_RWA3/A@RWADB CONTROL=MUREX_GL_TEMP.CTL EXTERNA L_TABLE=GENERATE_ONLY

SQL*LOADER: RELEASE 11.2.0.3.0 - PRODUCTION ON 星期四 9月 12 22:27:03 2013
COPYRIGHT (C) 1982, 2011, ORACLE AND/OR ITS AFFILIATES.? ALL RIGHTS RESERVED.

B.從LOG文件中提取DDL語句
CREATE TABLE "SYS_SQLLDR_X_EXT_MUREX_GL_TEMP"
(
? "AREANO" VARCHAR2(20),
? "CURRENCY" VARCHAR2(20),
? "APCODE" VARCHAR2(20),
? "ORGCDE" VARCHAR2(20),
? "DAMOUNT" VARCHAR2(200),
? "CAMOUNT" VARCHAR2(200),
? "REMAKR" VARCHAR2(200)
)
ORGANIZATION EXTERNAL
(
? TYPE ORACLE_LOADER? --指定外部表的訪問方式，9I不支持ORACLE_DATAPUMP
? DEFAULT DIRECTORY SQLDR? --目錄
? ACCESS PARAMETERS?? --配置外部表參數
? (
??? RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK? --記錄以換行為結束
??? BADFILE 'SQLDR':'MUREX_GLRCN_INIT_20130809_BADFILE.DAT'?? --存放處理失敗的記錄文件描述
??? DISCARDFILE 'SQLDR':'MUREX_GLRCN_INIT_20130809_DISFILE.DAT' --存放處理丟棄的記錄文件描述
??? LOGFILE 'MUREX_GL_TEMP.LOG_XT'?? --日志文件
??? READSIZE 1048576? --ORACLE讀取輸入數據文件所用的默認緩沖區,此處為MB,如專用模式則從PGA分配,如共享模式? 則從SGA分配
??? FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY " " LDRTRIM?? --"|"描述字段的終止符, " "作用符
??? REJECT ROWS WITH ALL NULL FIELDS?? -- --所有為空值的行被跳過并且記錄到BAD FILE
??? (??????????????????????????????????????????????????????????????????????????? ---下面是描述外部文件各個列的定義
????? "AREANO" CHAR(255)
??????? TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
????? "CURRENCY" CHAR(255)
??????? TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
????? "APCODE" CHAR(255)
??????? TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
????? "ORGCDE" CHAR(255)
??????? TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
????? "DAMOUNT" CHAR(255)
??????? TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
????? "CAMOUNT" CHAR(255)
??????? TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
????? "REMAKR" CHAR(255)
??????? TERMINATED BY "|" OPTIONALLY ENCLOSED BY " "
??? )
? )
? LOCATION
? (
??? 'MUREX_GLRCN_INIT_20130809.DAT'?? --描述外部文件的文件名
? )
)REJECT LIMIT UNLIMITED?????????? --描述允許的錯誤數，此處為無限制
;

WINDOWS換行是\r\n，十六進制數值是：0D0A
LINUX換行是\n，十六進制數值是：0A

所以我們確定分隔符先用UE查看分隔符,然后再改動創建外部表的語句,否則會加載不成功
由于在源文件換行符為0A,所以換行符應改為:
RECORDS DELIMITED BY 0X'0A'?? -- 0X表示十六進行,0A為LINUX換行符.
所以此方法適用于確定換行符.

五.外部表定義的幾個重點
A.ORGANIZATIONEXTERNAL關鍵字，必須要有。以表明定義的表為外部表。
B.重要參數外部表的類型
ORACLE_LOADER：定義外部表的缺省方式，只能只讀方式實現文本數據的裝載。
ORACLE_DATAPUMP：支持對數據的裝載與卸載，數據文件必須為二進制DUMP文件。可以從外部表提取數據裝載到內部表，也可以從內部表卸載數據作為二進制文件填充到外部表。
C.DEFAULTDIRECTORY：缺省的目錄指明了外部文件所在的路徑
D.LOCATION：定義了外部表的位置
F.ACCESS PARAMETERS：描述如何對外部表進行訪問
RECORDS關鍵字后定義如何識別數據行 ?
DELIMITEDBY'XXX'——換行符，常用NEWLINE定義換行，并指明字符集。對于特殊的字符則需要單獨定義，如特殊符號，可以使用OX'十六位值'，例如TAB(/T)的十六位是9，則DELIMITEDBY0X'09'；CR(/R)的十六位是D，那么就是DELIMITEDBY0X'0D'。 SKIP X ——跳過X行數據，有些文件中第一行是列名，需要跳過第一行，則使用SKIP 1。
FIELDS關鍵字后定義如何識別字段，常用的如下：
FIELDS：TERMINATEDBY'X'——字段分割符。
ENCLOSEDBY'X'——字段引用符，包含在此符號內的數據都當成一個字段。例如一行數據格式如："ABC","A""B,""C,"。使用參數TERMINATEDBY','ENCLOSEDBY'"'后，系統會讀到兩個字段，第一個字段的值是ABC，第二個字段值是A"B,"C,。
LRTRIM ——刪除首尾空白字符。
MISSING FIELDVALUESARENULL——某些字段空缺值都設為NULL。
對于字段長度和分割符不確定且準備用作外部表文件，可以使用ULTRAEDIT、EDITPLUS等來進行分析測試，如果文件較大，則需要考慮將文件分割成小文件并從中提取數據進行測試。
????? ?
外部表對錯誤的處理
REJECT LIMIT UNLIMITED
在創建外部表時最后加入LIMIT子句，表示可以允許錯誤的發生個數。默認值為零。設定為UNLIMITED則錯誤不受限制
BADFILE和NOBADFILE子句
用于指定將捕獲到的轉換錯誤存放到哪個文件。如果指定了NOBADFILE則表示忽略轉換期間的錯誤如果未指定該參數，則系統自動在源目錄下生成與外部表同名的.BAD文件BADFILE記錄本次操作的結果，下次將會被覆蓋
LOGFILE和NOLOGFILE子句
同樣在ACCESSPARAMETERS中加入LOGFILE'LOG_FILE.LOG'子句，則所有ORACLE的錯誤信息放入'LOG_FILE.LOG'中而NOLOGFILE子句則表示不記錄錯誤信息到LOG中，如忽略該子句，系統自動在源目錄下生成與外部表同名的.LOG文件

轉載于:https://www.cnblogs.com/chinaxin/p/3333174.html

總結

以上是生活随笔為你收集整理的Oracle的外部表的全部內容，希望文章能夠幫你解決所遇到的問題。

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