SQL*Loader headache when loading file with consecutive tab chars
Thomas Gaines
I seem to have tons of problems when attempting to do even basic
loading
using the SQL*Loader tool. I'm attempting to load a file that has a
single
tab character as its delimiter. In the file are several lines that
have two
tab characters adjacent to each other. This means that the value in
between these two characters is meant to be null when the load is
complete.
For whatever reason, SQL*Loader thinks that my delimiter is
whitespace!
Since one or more tabs or spaces can be whitespace, my two consecutive
tab characters are interpreted as a single whitespace delimiter. I
thought
that my control file was sufficient, but apparently it isn't.
Here is my control file:
LOAD DATA
INFILE 't:\product_cat_ops_prog.txt'
BADFILE 'u:\product_cat_ops_prog\product_cat_ops_prog.bad'
DISCARDFILE 'u:\product_cat_ops_prog\product_cat_ops_prog.dsc'
INTO TABLE satprod.product_cat_ops_prog
when (2:14) != 'datafile_name'
trailing nullcols
(datafile_name char terminated by X'9' optionally
enclosed by "'",
datafile_id integer external terminated by X'9',
operational_status char terminated by X'9' optionally
enclosed by "'",
coverage char terminated by X'9' optionally
enclosed by "'",
processing_name char terminated by X'9' optionally
enclosed by "'",
program_abbrev char terminated by X'9' optionally
enclosed by "'",
meas_cat_id integer external terminated by X'9',
product_group_id integer external terminated by X'9')
And a snippet from the log file:
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
DATAFILE_NAME FIRST * WHT O(') CHARACTER
DATAFILE_ID NEXT * WHT CHARACTER
OPERATIONAL_STATUS NEXT * WHT O(') CHARACTER
COVERAGE NEXT * WHT O(') CHARACTER
PROCESSING_NAME NEXT * WHT O(') CHARACTER
PROGRAM_ABBREV NEXT * WHT O(') CHARACTER
MEAS_CAT_ID NEXT * WHT CHARACTER
PRODUCT_GROUP_ID NEXT * WHT CHARACTER
Record 1: Discarded - failed all WHEN clauses.
Record 436: Rejected - Error on table SATPROD.PRODUCT_CAT_OPS_PROG.
ORA-01401: inserted value too large for column
And my table:
create table satprod.product_cat_ops_prog
(datafile_name varchar2(60) not null,
datafile_id number(5) not null,
operational_status varchar2(15) not null,
coverage varchar2(40) not null,
processing_name varchar2(40) null,
program_abbrev varchar2(10) null,
meas_cat_id number(5) null,
product_group_id number(5) null);
And the "bad" record from the input, with '^' representing a tab
character:
'Rainfall Estimates'^608^^'CONUS, HI, PR, Mex'^^'GOES'^7^126
The error is occuring because SQL*Loader is trying to jam the
"COVERAGE" field
(typically of 30 to 40 characters) into the table column
"OPERATIONAL_STATUS"
meant for only 15.
I'm doing all sorts of variations in my control file and nothing seems
to work for me.
I've tried the following things in the control file:
- putting in a "fields terminated by X'9'" clause in the main body
- changing X'9' to '\t' both in the main body and the individual
fields
- removing the "char" and "integer external" specs
Do you have a suggestion about how I can get SQL*Loader to behave?
Oracle version is 8.1.7.2. OS is Win2000.
Thanks,
Tom
george lewycky
Oreilly has a fantastic book on SQL LOADER out, it helped
me a great deal.
I suggest making a delimter such as "]" or something totally
unique that will not conflict with your data and the sqlldr
code.
I had the same problem with names, etc that had
embedded tick marks ( ' , " )
ex.
LOAD DATA
INFILE 'cust_spcl.txt'
replace INTO TABLE RA_CUSTOMERS_INTERFACE_ALL
FIELDS TERMINATED BY '","' TRAILING NULLCOLS
(
ORIG_SYSTEM_CUSTOMER_REF,CUSTOMER_NAME,ADDRESS1,
ADDRESS2,ADDRESS3,ADDRESS4,CITY,STATE,
POSTAL_CODE,COUNTRY,INSERT_UPDATE_FLAG,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,CREATION_DATE,CUSTOMER_CATEGORY_CODE,
CUSTOMER_STATUS,ORIG_SYSTEM_ADDRESS_REF,
PRIMARY_SITE_USE_FLAG,SITE_USE_CODE,
CUSTOMER_TYPE
)
good luck
george
thomas...@noaa.gov (Thomas Gaines) wrote in message news:<fb7f8a4a.02091...@posting.google.com>...
Thomas Gaines
Thanks for the reply. I'm normally amongst the first to pick up
new Oracle-related books, but I hadn't gotten that one yet.
Long ago, I figured that my infrequent use of SQL*Loader didn't
warrant a 30 or 40 dollar purchase.
Anyway, I was able to get around my problem by eliminating the
"optionally enclosed by" clause. My character data is surrounded by a
single quote, and every field (character and numbers) is separated
by a single tab character. That's not negotiable, unfortunately.
Anyway, I was able to work around my problem by removing the
"optionally enclosed by" clause and then trimming the single quotes
on each side of the character data.
Here is a sample control file:
LOAD DATA
INFILE 'u:\satellite\satellite.txt'
BADFILE 'u:\satellite\satellite.bad'
DISCARDFILE 'u:\satellite\satellite.dsc'
INTO TABLE satprod.satellite
FIELDS TERMINATED BY X'09'
trailing nullcols
(satellite_ID integer external,
satellite_name char "ltrim(rtrim(:satellite_name,''''),'''')",
orbit_type char "ltrim(rtrim(:orbit_type,''''),'''')",
satellite_status char "ltrim(rtrim(:satellite_status,''''),'''')",
satellite_status_remark char
"ltrim(rtrim(:satellite_status_remark,''''),'''')",
program_id integer external,
system_id integer external)
It's ugly, but it does what I want. I've opened a TAR with Oracle Support,
and I'll keep it open for a while to see what they have to say about it all.
It's hard to believe that I can't use my original control file with
the data that I had.
Bye,
Tom
gel...@nyct.com (george lewycky) wrote in message news:<68aecc05.02091...@posting.google.com>...