Re: sqlldr str format with WHEN clause into multiple tables

if anyone cares putting "position(1) in between "filler" and "char(2)" for the tbltype field made it work. So changing:

(tbltype filler char(2), tbldata)

to

(tbltype filler position(1) char(2) tbldata)

works.

rjm_at_si.rr.com (richie) wrote in message news:<5e010620.0409280622.7b7967ab_at_posting.google.com>...
> 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 Fri Oct 01 2004 - 11:36:43 CDT