SQL*Loader refuses to load second INTO block

From: Rich J <rjoralist3_at_society.servebeer.com>
Date: Thu, 22 Oct 2015 20:51:38 -0500
Message-ID: <e45e6f82f8f3b54d28aeeb4a60e10142_at_society.servebeer.com>

 

Hey all,

I'm using SQL*Loader 11.2.0.3 to dump a pipe-delimited report with multiple formats (e.g. block headings and detail rows) into multiple tables, using WHEN to split them up. Here's a sanitized version of the controlfile:

OPTIONS (READSIZE=4194304, BINDSIZE=4194304, ROWS=512, ERRORS=99999999, SKIP=1)
LOAD DATA
INFILE 'GLStuff.txt'
APPEND
INTO TABLE gl_accounts
 WHEN gl_cat = 'Expenses'
 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(

gl_cat CHAR,
gl_acct CHAR,

filler00 FILLER,
filler01 FILLER,
filler02 FILLER,
filler03 FILLER,
filler04 FILLER,
filler05 FILLER,

gl_acct_desc CHAR
)
INTO TABLE gl_accounts
 WHEN gl_cat = 'Cost of Sales'
 FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(

gl_cat CHAR,
gl_acct CHAR,

filler00 FILLER,
filler01 FILLER,
filler02 FILLER,
filler03 FILLER,
filler04 FILLER,
filler05 FILLER,

gl_acct_desc CHAR
)

The two INTO blocks are necessary since SQL*Loader's WHEN clause has no "OR" operator. The kicker here is that only the first INTO block ever succeeds. If I switch the two INTO blocks around, only the new first one succeeds. From the log:

Table GL_ACCOUNTS:
 71 Rows successfully loaded.
 0 Rows not loaded due to data errors.
 13875 Rows not loaded because all WHEN clauses were failed.  0 Rows not loaded because all fields were null.

Table GL_ACCOUNTS:
 0 Rows successfully loaded.
 0 Rows not loaded due to data errors.
 13946 Rows not loaded because all WHEN clauses were failed.  0 Rows not loaded because all fields were null.

The 71 rows are perfect for the 'Expenses'. When I switch the 'Cost of Sales' block to be first, the number of rows is an also-correct 305, again with the second INTO block being ignored. The rest of the 13K+ rows are correctly being excluded in this example, as they have neither 'Expenses' nor 'Cost of Sales' in the first field ("gl_cat").

It seems like I have an error in my control file somehow, but after a day's search, I can't seem to find it. Yeah, I know I can run it twice, or have multiple control files -- it's only 13K rows. But I'm curious as to what I'm doing wrong here, because it sure doesn't seem obvious (to me).

Anyone?

Rich

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

Received on Fri Oct 23 2015 - 03:51:38 CEST