Sorry, the: "CD",1
at the end of the line reading: (tbltype filler char(2), tbldata)"CD",1
is a cut and past error
it's really just: (tbltype filler char(2), tbldata)
in the ctl file without the: "CD",1
thanks again.
rjm_at_si.rr.com (richie) wrote in message news:<5e010620.0409271219.5e9dbe46_at_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
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> 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 Tue Sep 28 2004 - 09:22:13 CDT
