日期 帳號 金額
--------------------------
20071231|202143|1000.00
20071231|201281|2000.00
20071231|201633|3000.00
20071231|201856|4000.00
20071231|200117|5000.00
20071231|200923|100.00
20071231|200975|200.00
20071231|201838|300.00
20071231|202134|400.00
20071231|201642|1000.00
20071231|200432|5000.00
20071231|201311|1120.00
20071231|202245|5500.00
20071231|202646|6200.00
20071231|202911|300.00
20071231|202935|51.23
20071231|200227|23.21
--------------------------
35194.44
/* 前兩行HEADER,後兩行TRAIL(PAGE FOOTER)建立EXTERNAL TABLE時的語法如下:
CREATE TABLE BANKDATA
(
BAL_DATE INTEGER,
ACCOUNT CHAR(6),
AMT NUMERIC(19,2)
) organization external
(type oracle_loader
default directory ETL_EXT_TABS
access parameters
(
records delimited by newline
SKIP 2
fields terminated by '|'
optionally enclosed by '"'
missing field values are null
)
location ('BANKDATA.TXT'))
reject limit unlimited;
/*
SKIP 2 指令可以告訴ORACLE 在FLAT FILE:BANKDATA.TXT裡頭兩行是不要的, 要跳過. 但是TRAIL的 虛線與總計則會被丟到.BAD去. 每次對BANKDATA做SELECT 時都會覆寫這個.BAD,並產生一個.LOG
SUCH AS MY EXAMPLE, I'LL HAVE TWO FILES 由ORACLE自動產生
BANKDATA_2856_3560.bad
BANKDATA_2856_3560.log
*/
/* BANKDATA_2856_3560.bad的內容如下: BANKDATA_2856_3560.log: SELECT 幾次, 會留下多少LOG(以下算一次)*/
------------------------
total
LOG file opened at 04/29/08 12:08:34
Field Definitions for table BANKDATA
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
BAL_DATE CHAR (255)
Terminated by "|"
Enclosed by """ and """
Trim whitespace same as SQL Loader
ACCOUNT CHAR (255)
Terminated by "|"
Enclosed by """ and """
Trim whitespace same as SQL Loader
AMT CHAR (255)
Terminated by "|"
Enclosed by """ and """
Trim whitespace same as SQL Loader
error processing column BAL_DATE in row 20 for datafile C:\oracle\product\10.2.0\ora_externaltable\BANKDATA.TXT
ORA-01722: 無效的數字
error processing column BAL_DATE in row 21 for datafile C:\oracle\product\10.2.0\ora_externaltable\BANKDATA.TXT
ORA-01722: 無效的數字
