External Table Creation - Fixed Length
I have been tasked with importing fixed-length data from a text file into an oracle table. In order to do that, i am creating an External Table. I am finally to the point where the script works to create the table, but when i run a select statement to "select * from external table", i receive only one row/record (the first row in the text file).
This is how the script looks:
CREATE TABLE EXT_MD
(
AR_SID VARCHAR2(12),
AR_SSN VARCHAR2(4),
AR_LASTNAME VARCHAR2(29),
AR_FIRSTNAME VARCHAR2(27),
AR_RACE VARCHAR2(1),
AR_DOB DATE,
NA_LASTNAME VARCHAR2(29),
NA_FIRSTNAME VARCHAR2(27),
NA_SEX VARCHAR2(1),
ID_FBI_ID VARCHAR2(9),
AR_ARR_DATE VARCHAR(26),
AR_PRIMARY_CHARGE VARCHAR2(35),
DEM_STREET_NBR VARCHAR2(5),
DEM_STREET_NAM VARCHAR2(10),
DEM_STREET_SFX VARCHAR2(2),
DEM_STREET_DIR VARCHAR2(2),
DEM_CITY VARCHAR2(10),
DEM_STATE VARCHAR2(2),
DEM_ZIP VARCHAR2(5),
AR_TRACK_NUM VARCHAR2(12),
DEM_ORI VARCHAR2(9)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE EXT_DIR:'ext_bag.bad'
LOGFILE EXT_DIR:'ext_good.log'
FIELDS
MISSING FIELD VALUES ARE NULL
(
AR_SID(1:12) CHAR(12),
AR_SSN(13:16) CHAR(4),
AR_LASTNAME(17:45) CHAR(29),
AR_FIRSTNAME(46:72) CHAR(27),
AR_RACE(73:73) CHAR(1),
AR_DOB(74:83) CHAR(10) DATE_FORMAT DATE MASK "YYYY-MM-DD",
NA_LASTNAME(84:112) CHAR(29),
NA_FIRSTNAME(113:139) CHAR(27),
NA_SEX(140:140) CHAR(1),
ID_FBI_ID(141:149) CHAR(9),
AR_ARR_DATE(150:175) CHAR(26),
AR_PRIMARY_CHARGE(176:210) CHAR(35),
DEM_STREET_NBR(211:215) CHAR(5),
DEM_STREET_NAM(216:225) CHAR(10),
DEM_STREET_SFX(226:227) CHAR(2),
DEM_STREET_DIR(228:229) CHAR(2),
DEM_CITY(230:239) CHAR(10),
DEM_STATE(240:241) CHAR(2),
DEM_ZIP(242:246) CHAR(5),
AR_TRACK_NUM(247:258) CHAR(12),
DEM_ORI(259:267) CHAR(9)
)
)
LOCATION ('daily.txt')
)
REJECT LIMIT UNLIMITED;
This is what part of the logfile looks like:
Field Definitions for table EXT_MD
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:
AR_SID CHAR (12)
Record position (1, 12)
Trim whitespace from right
AR_SSN CHAR (4)
Record position (13, 16)
Trim whitespace from right
etc (shows the rest of the columns with similar message) Then at the end of the logfile
Column Transformations
ROWID
Date Cache Statistics for table EXT_MD
Max Size: 1000
Entries : 1
Hits : 0
Misses : 0
Any help or direction will most certainly be appreciated. Thanks in advance.
Edited by: zephyr223 on Mar 25, 2013 10:47 AM