Home » RDBMS Server » Server Utilities » SQL Loader related question (urgent)
SQL Loader related question (urgent) [message #368104] Wed, 29 March 2000 13:27 Go to next message
Anita
Messages: 21
Registered: March 2000
Junior Member
I am loading the data from fixed length file to Oracle Database.

My control file is something like this:

LOAD DATA

APPEND

INTO TABLE Table1
(
row_id SEQUENCE(1),
Field1 POSITION(24:85),
Field2 POSITION(88:110),
numField "FLOOR((rownum-1)/20000)+100",
)

INTO TABLE Table2
(
row_id SEQUENCE(1),
Field1 POSITION(24:85),
Field2 POSITION(88:110),
numField "FLOOR((rownum-1)/20000)+100",
)

Everything works fine, except the numField. I want to load different number into this field for every 20000 rows. With the above equation it should start from 100 and go on. But looks like rownum is always being returned as 0 and hence it puts 99 into numField for every row.

Could anybody please let me know what is the problem and solution for this. If you have any other way of doing this also fine, please suggest.

Thanks in advance.
Re: SQL Loader related question (urgent) [message #368105 is a reply to message #368104] Thu, 30 March 2000 08:32 Go to previous messageGo to next message
hmg
Messages: 40
Registered: March 1999
Member
Hi,

look at this ctl-file. It worked on my environment

LOAD DATA

infile 'd:\temp\loader\anita.txt'
badfile 'd:\temp\loader\anita.bad'
discardfile 'd:\temp\loader\anita.dsc'

replace

INTO TABLE test
(
row_id SEQUENCE(1),
col1 position(1:2),
numField "floor((:row_id-1)/10) + 100"
)
Re: SQL Loader related question (urgent) [message #368106 is a reply to message #368105] Thu, 30 March 2000 12:43 Go to previous message
Anita
Messages: 21
Registered: March 2000
Junior Member
Thanks for the reply, I appreciate it.

You are using row_id, not necessarily it starts with 1 (Am I right?)

The number goes into numField has to start with a fixed number. I should know that in advance. If I use row_id, I will not know that in advance.

Or did I not get anything right here?

The equation, I used using rownum works fine in a UPDATE sql query, but it doesn't work if I specify it in the SQL Loader control file.
Previous Topic: Re: import problem
Next Topic: Error while starting the database
Goto Forum:
  


Current Time: Tue Jan 31 08:03:03 CST 2023