Re: SQLLDR syntax question

From: ddf <oratune_at_msn.com>
Date: Sun, 7 Mar 2010 15:02:04 -0800 (PST)
Message-ID: <589fb3d1-2dc6-48dc-a7aa-6a209155c296_at_u5g2000prd.googlegroups.com>

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 Received on Sun Mar 07 2010 - 17:02:04 CST