Re: SQL*Loader ignoring additional columns when delimited

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Tue, 01 Mar 2005 11:27:54 -0500
Message-Id: <s22451c9.046@gwia.galottery.org>

Scott,
have you described the BAD file location in your .PAR file? Does the load stop after the first error? Maybe setting the errors= parameter higher will allow to complete the load.

Ron

>>> <scott.hutchinson_at_interact-analysis.com> 03/01/2005 12:44:01 PM
>>>

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
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 01 2005 - 11:32:24 CST