sqlldr str format with WHEN clause into multiple tables

From: richie <rjm_at_si.rr.com>
Date: 27 Sep 2004 13:19:47 -0700
Message-ID: <5e010620.0409271219.5e9dbe46@posting.google.com>

Using sqlldr 9.2.0.5.0 on redhat linux (advanced sever. Trying to load a comma separated file into multiple tables with the first field indicating a code for the WHEN clause for each table.

control file:

load data
infile test.dat "str X'0a'"
badfile test.bad
discardfile test.dsc
append
into table testa when tbltype='CD'
fields terminated by X'2c' optionally enclosed by '"' trailing nullcols
(tbltype filler char(2), tbldata)
into table testb when tbltype='IC'
fields terminated by X'2c' optionally enclosed by '"' trailing nullcols
(tbltype filler char(2), tbldata)"CD",1

data file:
"CD",1
"IC",2

The only way I can get this to work is to make it one logical record by removing the "STR X'0a'" option and having the data file look like:

"CD",1,"IC",2

The problem here is that this breaks for big files because sqlldr has a max size on the logical record.

I really want to have each line written to the field terminated with a 0x0a control char and have that line considered one logical record by sqlldr:

so:

"CD",1
"IC",2

is considered two records.

Each line ends in 0x0a as shown by hexdump:

000000 22 43 44 22 2c 31 0a 22 - 49 43 22 2c 32 0a
"CD",1."IC",2.

When I run sqlldr it reads two logical records which is correct but it fails to align the second filler field in the second record to make the correct WHEN comparison and discards the second record:

SQL*Loader: Release 9.2.0.5.0 - Production on Mon Sep 27 16:13:00 2004

Control File: test.ctl
Data File: test.dat
File processing option string: "str X'0a'" Bad File: test.bad
Discard File: test.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     10000 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TESTA, loaded when TBLTYPE = 0X4344(character 'CD') Insert option in effect for this table: APPEND TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
TBLTYPE                             FIRST     2   ,  O(") CHARACTER
  (FILLER FIELD)
TBLDATA                              NEXT     *   ,  O(") CHARACTER

Table TESTB, loaded when TBLTYPE = 0X4943(character 'IC') Insert option in effect for this table: APPEND TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
TBLTYPE                              NEXT     2   ,  O(") CHARACTER
  (FILLER FIELD)
TBLDATA                              NEXT     *   ,  O(") CHARACTER

Record 2: Discarded - failed all WHEN clauses.

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

Table TESTB:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  2 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded

Space allocated for bind array:                 255712 bytes(488 rows)

Read buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        1

Run began on Mon Sep 27 16:13:00 2004
Run ended on Mon Sep 27 16:13:00 2004

Elapsed time was:     00:00:00.05
CPU time was:         00:00:00.03

because all fields were null.

You will also notice in the above log that the second table shows
"NEXT" as the position instead of "FIRST" like the first table for the
tbltype field.

Any help would be greatly appreciated.
Thanks.
-richie Received on Mon Sep 27 2004 - 15:19:47 CDT