SQLLDR syntax question

From: Martin Frodderrer <martinfridderrer_at_googlemail.com>
Date: Sun, 7 Mar 2010 06:17:10 -0800 (PST)
Message-ID: <17a21bf4-9f1b-47cf-aaf9-860ab3761d36_at_g7g2000yqe.googlegroups.com>

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