External Table with fixed length using Oracle10g

Here is my flat file:BANKDATA3.TXT, fixed length and with header and trailer =>



日期 帳號 金額
------------------------
200712312021430001000.00
200712312012810002000.00
200712312016330003000.00
200712312018560004000.00
200712312001170005000.00
200712312009230000100.00
200712312009750000200.00
200712312018380000300.00
200712312021340000400.00
200712312016420001000.00
200712312004320005000.00
200712312013110001120.00
200712312022450005500.00
200712312026460006200.00
200712312029110000300.00
200712312029350000051.23
200712312002270000023.21
------------------------

Here is how to create external table bankdata3=>

CREATE TABLE BANKDATA3
(
BAL_DATE INTEGER,
ACCOUNT CHAR(6),
AMT NUMERIC(10,2)
) ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY ETL_EXT_TABS
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
SKIP 2
BADFILE 'BANKDATA3.BAD'
LOGFILE 'BANKDATA3.LOG'
FIELDS LRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL
FIELDS(BAL_DATE (1:8),
ACCOUNT (9:14),
AMT (15:24)
)
)
LOCATION ('BANKDATA3.TXT'))
REJECT LIMIT UNLIMITED;

※ Remove that flat file will cause error(ORA-29913,ORA-29400,KUP-04040,ORA-06512). Just put it back and you don't have to recompile your procedure which contain that external table.

External Table using Oracle 10g

/* 先看一下BANKDATA.TXT的內容: */

日期 帳號 金額
--------------------------
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: 無效的數字


check database object status using DB2 UDB 8.2

/* check view status */

SELECT VIEWSCHEMA,VIEWNAME,VALID,VARCHAR(TEXT)
FROM SYSCAT.VIEWS
WHERE VIEWSCHEMA = 'ADMINISTRATOR'


/* check procedures if function recreate then recreate procedure is needed */

SELECT ROUTINENAME FROM sysibm.sysroutines
WHERE VARCHAR(TEXT) LIKE '%FN_CHKVALIDATE%'


/* 檢查可能失效的PROCEDURE */

SELECT *
FROM SYSIBM.SYSDEPENDENCIES
WHERE DSCHEMA = 'ORION'
AND BNAME NOT IN (SELECT PKGNAME FROM SYSCAT.PACKAGEDEP WHERE BTYPE = 'T')
AND BTYPE = 'K'

/* 檢查那些Stored Procedures用了哪些Functions */

SELECT PROCNAME,FUNCNAME
FROM SYSCAT.PROCEDURES,
(SELECT DNAME SYSDNAME,BNAME SYSNAME FROM SYSIBM.SYSDEPENDENCIES) T,
(SELECT PKGNAME,BNAME PKGBNAME FROM SYSCAT.PACKAGEDEP) T1,
(SELECT FUNCNAME,SPECIFICNAME FUNCSPECIFICNAME FROM SYSCAT.FUNCTIONS) T2
WHERE SPECIFICNAME = T.SYSDNAME
AND T.SYSNAME = T1.PKGNAME
AND T1.PKGBNAME = T2.FUNCSPECIFICNAME;


/* 檢查那些Stored procedure 用了哪些Tables */

SELECT T.DNAME,A.BNAME
FROM SYSCAT.PACKAGEDEP A,
(SELECT BNAME,DNAME from SYSIBM.SYSDEPENDENCIES WHERE DSCHEMA = 'ORION') T
WHERE BTYPE = 'T' AND PKGNAME = T.BNAME


/* 如何檢持哪些VIEW使用哪些TABLES,且是否為VALID */

SELECT A.VIEWNAME,A.BNAME,B.VALID
FROM SYSCAT.VIEWDEP A,SYSCAT.VIEWS B
WHERE A.VIEWNAME = B.VIEWNAME
AND B.DEFINER = 'ORION'