SQL*Loader ignoring additional columns when delimited

From: <scott.hutchinson_at_interact-analysis.com>
Date: Tue, 1 Mar 2005 17:44:01 +0000
Message-ID: <1109699041.4224a9e15a4f1@mopp.namemagic.com>

All,

I have a problem where SQL*Loader is loading rows from a delimited file, even when the file erroneously contains too many delimiters. In this case, I would like SQL*Loader to treat the row as invalid and write it to it's "bad" file.

Does anyone know a way of forcing SQL*Loader to reject rows from a file that contains too many delimiters? We can validate the file prior to loading it, but I prefer to avoid this due to the time involved.

EG (Oracle vsn 9.2.0.4):

Input file (columns delimited by "]" ) - contains 3 fields cat delim_test.dat
AAA]BBB]CCC Table has 2 columns
desc delim_test
 Name Null? Type

• -------- ------------ FLD1 VARCHAR2(10) FLD2 VARCHAR2(10)

SQLLdr reports:
Table DELIM_TEST:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null.

Table contains:
select * from delim_test;

FLD1 FLD2
---------- ----------
AAA BBB Thanks,
Scott Hutchinson.

::This message sent using the free Web Mail service from http://TheName.co.uk

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 01 2005 - 11:17:03 CST