SQL Loader doesn't recognize empty data file columns

[aldovalerio]

Windows 2000/ Oracle 8.1.6

I'm trying to load a tab-delimited file into a table, but when it encounters a record with an empty column, it seems to ignore it and errors out on "Record 19: Rejected - Error on table SRADB.SAP_EXTRACT, column REVISION_CODE. Field in data file exceeds maximum length".

CREATE TABLE SRADB.SAP_EXTRACT (
SKU_ID VARCHAR2 (18),
COST_CENTER_CODE CHAR (4),
PRIME_MARKET_CODE CHAR (2) NOT NULL,
MARKET_TYPE_CODE CHAR (1) NOT NULL,
FAMILY_CODE CHAR (2) NOT NULL,
BRAND_CODE VARCHAR2 (4) NOT NULL,
PACK_TYPE_CODE CHAR (3) NOT NULL,
CIG_LENGTH VARCHAR2 (9),
CFT_ID VARCHAR2 (18),
BLEND_CODE VARCHAR2 (15),
REVISION_CODE CHAR (1),
EFFECTIVE_DATE VARCHAR2 (15))

My SQL Loader control file:

OPTIONS (SKIP=2) -- Skip first 2 records (column titles)
LOAD DATA
INFILE 'SRA_SUPPLIERSDB_SAP_EXTRACT.DAT'
BADFILE 'SRA_SUPPLIERSDB_SAP_EXTRACT.BAD'
DISCARDFILE 'SRA_SUPPLIERSDB_SAP_EXTRACT.DSC'
REPLACE
INTO TABLE sradb.sap_extract
FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( COST_CENTER_CODE CHAR(4),
ALT_BOM_CODE FILLER CHAR,
ALT_BOM_DESC FILLER CHAR,
SKU_ID CHAR(18),
FAMILY_CODE CHAR(2),
FAMILY_DESC FILLER CHAR,
BRAND_CODE CHAR(4) "RTRIM(LTRIM(SUBSTRBRAND_CODE,1,4)))",
BRAND_DESC FILLER CHAR,
PACK_TYPE_CODE CHAR(3),
PACK_STYLE_CODE FILLER CHAR,
CIG_LENGTH CHAR(9) "RTRIM(LTRIMCIG_LENGTH))",
PRIME_MARKET_CODE CHAR(2),
MARKET_TYPE_CODE CHAR(1),
CFT_ID CHAR(18),
CFT_DESC FILLER CHAR,
REVISION_CODE CHAR(1),
EFFECTIVE_DATE CHAR(10),
BLEND_CODE CHAR(15)
)

SQL loader command:
sqlldr /@SRADEV direct=false control='SRA_SUPPLIERSDB_SAP_EXTRACT.ctl' log='SRA_SUPPLIERSDB_SAP_EXTRACT.log'

I found the problem earlier when I had coded a control file COLUMN1 FILLER CHAR to handle the first column which is empty on all records. This caused errors throughout because it was trying to load data file col. 2 as col. 1, col. 3 as col. 2, etc. I've verified that the data has the tab character between these empty columns.

Is there a parameter/option that controls whether NULL columsn are ignored? It seems that I'm missing something very basic.

 

[aldovalerio]

I found a solution thanks to Barbara Boehmer at

http://www.orafaq.com

: removing the OPTIONALLY ENCLOSED BY '"' did the trick. Fortunately I didn't have any double-quotes around my data.