OraFAQ Forum: Server Utilities » variable in sqlldr

Home » RDBMS Server » Server Utilities » variable in sqlldr - help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

variable in sqlldr - help [message #152039]

Tue, 20 December 2005 11:29

ozzy80
Messages: 55
Registered: June 2005

Member

Hi all,

I have a data file and I need to load the data to a table. I can do that using sqlldr. But in that table I also need to load an additional column (which is an URL) whose lost value depends on the first column value of the row...

example:

data file....

1|2|3|4

table columns...

NUM1 number
NUM2 number
NUM3 number
NUM4 number
URL varchar2

the value of the URL will be "http://test.com/class='NUM1 value'

so can my control file look like this?

OPTIONS(ERRORS = 99999)
LOAD DATA
DISCARDMAX 99999
APPEND INTO TABLE test
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
(NUM1,
NUM2,
NUM3,
NUM4,
URL CONSTANT 'http://test.com/class=':NUM1)

Please help!!!

Thanks,

Ozzy

Report message to a moderator

Re: variable in sqlldr - help [message #152040 is a reply to message #152039]

Tue, 20 December 2005 12:00

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

oracle@mutation#sqlldr userid=scott/tiger control=myctl.ctl

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Dec 20 11:58:25 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 3
oracle@mutation#sqlplus -s scott/tiger
column URL format a30
select * from mytable;

      NUM1       NUM2       NUM3       NUM4 URL
---------- ---------- ---------- ---------- ------------------------------
         1          2          3          4 http://test.com/class=1
         5          6          7          8 http://test.com/class=5
         9         10         11         12 http://test.com/class=9

exit
oracle@mutation#cat myctl.ctl
LOAD DATA
INFILE 'mydatafile.data'
append
INTO TABLE mytable
fields terminated by '|' optionally enclosed by '"' trailing nullcols
(
NUM1,
NUM2,
NUM3,
NUM4,
URL CHAR "'http://test.com/class='||:NUM1"  )

Report message to a moderator

Re: variable in sqlldr - help [message #152043 is a reply to message #152040]

Tue, 20 December 2005 12:13

ozzy80
Messages: 55
Registered: June 2005

Member

thank u so much Mahesh!!!

Report message to a moderator

Previous Topic:	Partition source
Next Topic:	stop running query in sql*plus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 05:55:58 CST 2024