I'm using external tables everyday to load csv files but I'm having great difficulty loading my first FIXED WIDTH file. I've tried several different access parameter variations and the CREATE TABLE command compiles without error but when I select the table, I get the following error. In my several iterations, I may get a different "KUP-01005: syntax error" but I can't seem to get past this:
Error Message:
select * from rzedelq;
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "badfile": expecting one of: "all, column, convert_error, csv, date_format, enclosed, ignore_chars_after_eor, (, ltrim, lrtrim, ldrtrim, missing, notrim, nullif, number_format, numeric_characters, optionally, rtrim, remove_quotes, reject, terminated, truncate_columns"
KUP-01007: at line 2 column 13
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
--
CREATE TABLE rzedelq
(
rzedelq_type VARCHAR2(1),
rzedelq_dob DATE,
rzedelq_last_name VARCHAR2(30),
rzedelq_first_name VARCHAR2(30),
rzedelq_balance NUMBER(6)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data
ACCESS PARAMETERS
(
RECORDS FIXED 76 FIELDS
BADFILE 'rzedelq.bad'
LOGFILE 'rzedelq.log'
MISSING FIELD VALUES ARE NULL
(
rzedelq_record_type (01:01) CHAR(1),
rzedelq_dob (02:09) CHAR(8) DATE 'yyyymmdd' NULLIF rzedelq_dob='00000000',
rzedelq_last_name (10:39) CHAR(30),
rzedelq_first_name (40:69) CHAR(30),
rzedelq_balance (70:75) NUMBER(6)
)
)
LOCATION ('rzedelq.dat')
)
REJECT LIMIT UNLIMITED
;
Sample data file:
--'rzedelq.dat'--
119811218ANDERSEN AIMEE 366910
219121006COWGER AMANDA 030900
119030707GESLIN ANDREA 150910
319041125HATFIELD CARRIE 055900
119150913MERRELL CONNIE 018920
419761024MESSINGER JASON 010960
119170708PRIMROSE JOHN 030920
519980721REEVES KAILYNN 018930
119690511SAFARIK ROBERT 021980
--
Any Ideas? Any help?
Does this help
SQL> create table ext_table_fixed (
2 rzedelq_type VARCHAR2(1),
3 rzedelq_dob DATE,
4 rzedelq_last_name VARCHAR2(30),
5 rzedelq_first_name VARCHAR2(30),
6 rzedelq_balance NUMBER(6)
7 )
8 organization external (
9 type oracle_loader
10 default directory ctmp
11 access parameters (
12 records delimited by newline
13 fields (
14 rzedelq_type (01:01),
15 rzedelq_dob (02:09) DATE 'yyyymmdd' NULLIF rzedelq_dob='00000000',
16 rzedelq_last_name (10:39),
17 rzedelq_first_name (40:69),
18 rzedelq_balance (70:75)
19 )
20 )
21 location ('fixed.dat')
22 )
23 reject limit unlimited;
Table created.
SQL>
SQL> select * from ext_table_fixed;
R RZEDELQ_D RZEDELQ_LAST_NAME RZEDELQ_FIRST_NAME RZEDELQ_BALANCE
- --------- ------------------------------ ------------------------------ ---------------
1 18-DEC-81 ANDERSEN AIMEE 366910
2 06-OCT-12 COWGER AMANDA 30900
1 07-JUL-03 GESLIN ANDREA 150910
3 25-NOV-04 HATFIELD CARRIE 55900
1 13-SEP-15 MERRELL CONNIE 18920
4 24-OCT-76 MESSINGER JASON 10960
1 08-JUL-17 PRIMROSE JOHN 30920
5 21-JUL-98 REEVES KAILYNN 18930
1 11-MAY-69 SAFARIK ROBERT 21980
9 rows selected.