Rich,
I seem to recall beating my head against that particular issue quite a while
back.
A quick google refreshed my memory. When reading delimited records, SQL*Loader
does not rewind back to the first position to evaluate the next “when” clause.
Believe it or not, the old 9.2 docs describe the issue better than anything
current I could find.
https://docs.oracle.com/cd/B10501_01/server.920/a96652/ch05.htm#1005802
I seem to remember using positional notation for my WHEN clauses, but that was
a few jobs ago so I don’t have access to those control files. You have a
variable length first field, but I’m sure there is a workaround buried in the
docs.
Hope this helps.
Gary Gruber
RPM Direct
www.rpmdirectllc.com<http://www.rpmdirectllc.com/>
www.exlservice.com<http://www.exlservice.com>
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Rich J
Sent: Thursday, October 22, 2015 9:52 PM
To: Oracle L
Subject: SQL*Loader refuses to load second INTO block
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
