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

Martin Frodderrer wrote:

I think you may have more luck with another tool, but sql*loader is not really suited for this task.

On Mar 7, 9:17*am, Martin Frodderrer <martinfridder... (AT) googlemail (DOT) com>
wrote:
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

On Mar 8, 12:02*am, ddf <orat... (AT) msn (DOT) com> wrote:
A shoot in the dark but...

Maybe BOUNDFILLER + EXPRESSION is what you are looking for:

http://carlosal.wordpress.com/2007/0...s-boundfiller/

HTH

Cheers.

Carlos.

Hi Martin,
you need a function that calculate your "string" data.
That's my try:

create or replace function fn_kk_calc ( string_in IN varchar2 ) return
number
is
erg number;
begin
execute immediate 'select ' || string_in || ' from dual' into erg;
return erg;
end;
/


LOAD DATA
INFILE math.txt
INTO TABLE kk_math
REPLACE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
id1 integer external "fn_kk_calc(:id1)",
id2 integer external
)

hth
Kay

On Mar 8, 9:56*am, Kay Kanekowski <kay.kanekow... (AT) web (DOT) de> wrote:
This all seems to be far more work than necessary simply to enable the
use of some round-about 'calculation' rather than coding the numeric
values desired. Yes, it's creative, but having to write a funciton to
process the mathematical gyrations loaded into the inline data
unnecessarily complicates a fairly basic data load. Has no one heard
of Occam's Razor? Paraphrased:

"The simplest solution is the best"

It's simplest in this situation to code 10 rather than 1+9;
interesting as the other offerings may be they are merely baroque
additions to what should be a minimalist execution.


David Fitzjarrell

On 9 Mar, 22:07, ddf <orat... (AT) msn (DOT) com> wrote:
Agreed. However, we don't really know the OP's requirement and why he
perhaps needs a way of loading the data as is.

-g

On Mar 10, 10:03*am, gazzag <gar... (AT) jamms (DOT) org> wrote:
It isn't a business reason:

"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.

...."

I have no problem with experimentation (this is how many people learn
things) however writing a function to aid SQL*Loader to process inline
data seems a bit excessive.

Of course that's only my opinion.


David Fitzjarrell