Re: SQLLDR syntax question

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Mon, 8 Mar 2010 00:25:39 -0800 (PST)
Message-ID: <6697335d-f335-40a1-888c-5e892891ceae_at_q16g2000yqq.googlegroups.com>

On Mar 8, 12:02 am, ddf <orat..._at_msn.com> wrote:
> On Mar 7, 9:17 am, Martin Frodderrer <martinfridder..._at_googlemail.com>
> wrote:
>
>
>
> > Hello. This is my first post to this group so if the question is too
> > simple, be gentle with me.
>
> > I have created a table called test like this "create table test(id1
> > number, id2 number)" in Oracle 10g.2 Standard Edition on a Windows XP
> > home computer.
>
> > I am trying to import data into my "test" table.
>
> > My SQLLDR control file looks like this. It is representative of my
> > test data but not my test data.
>
> > LOAD DATA
> > INFILE *
> > INTO TABLE test
> > REPLACE
> > FIELDS TERMINATED BY ','
> >        OPTIONALLY ENCLOSED BY '"'
> > (
> >   id1 integer external,
> >   id2 integer external
> > )
> > BEGINDATA
> > 1+9,    400
> > "2*10", 401
> > 3+8,    402
> > 4,      403
> > 5,      404
>
> > When I run SQLLDR, all I get imported into my "test" table is the rows
> > with 4,403 and 5,404. I want the additional rows containing
> > 10, 400
> > 20, 401
> > 11, 402
> > included in the import too. I can't figure out how to do it.
>
> > What I have tried is, in my SQLLDR control file, changing
>
> >   id1 integer external,
>
> > to
>
> > id1 integer expression "to_number(:id)",
>
> > this doesn't work.
>
> > I have also tried
>
> > id1 integer expression "select :id1 from dual",
>
> > but this doesn't work either.
>
> > I am new to this. Can someone help?
>
> > Thank you
>
> > Martin
>
> You should do the obvious and rewrite your control file in this
> manner:
>
> LOAD DATA
> INFILE *
> INTO TABLE test
> REPLACE
> FIELDS TERMINATED BY ','
>        OPTIONALLY ENCLOSED BY '"'
> (
>   id1 integer external,
>   id2 integer external
> )
> BEGINDATA
> 10,    400
> 20,    401
> 11,    402
> 4,      403
> 5,      404
>
> Expressions such as 1+9, "2*10" and  3+8 are not integers to Oracle,
> they are strings.  Provide integers, as shown above, and you will have
> all five rows loaded.
>
> David Fitzjarrell

>>"It is representative of my test data but not my test data. "

A shoot in the dark but...

Maybe BOUNDFILLER + EXPRESSION is what you are looking for:

http://carlosal.wordpress.com/2007/09/24/sqlloader-y-when-la-palabra-clave-es-boundfiller/

HTH Cheers.

Carlos. Received on Mon Mar 08 2010 - 02:25:39 CST