On 2015/10/22 22:18, Gary Gruber wrote:
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 [1]
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
)
