OraFAQ Forum: Server Utilities » How to write a control file to concatenate or add fileds (sql Loader)

Home » RDBMS Server » Server Utilities » How to write a control file to concatenate or add fileds (sql Loader)

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

How to write a control file to concatenate or add fileds (sql Loader) [message #170273]

Wed, 03 May 2006 01:19

wdaccache
Messages: 1
Registered: May 2006

Junior Member

Hi All,

I have a flat file as follows:

ID,Name1,Name2,height1,height2
12,Test,X,12,13
13,Test2,Y,14,16

I need an sql loader control file that concatenates Name1 and Name2 into one db field and add up height1 and height 2 into one field so that I can get the following result in the following table:

ID, Name, Height
12, TestX, 25
13, Test2Y, 30

Thanks a lot for providing me with a CTL file example that can do that.

Regards,
Walid

Report message to a moderator

Re: How to write a control file to concatenate or add fileds (sql Loader) [message #170602 is a reply to message #170273]

Thu, 04 May 2006 08:33

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

Senior Member
Account Moderator

oracle@mutation#desc mutation scott.sometable

Table:scott.sometable
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 C1                                           NUMBER
 C2                                           VARCHAR2(10)
 C3                                           NUMBER

oracle@mutation#cat somectl.ctl
LOAD DATA
infile 'somedata.data'
truncate INTO TABLE sometable
FIELDS TERMINATED BY ","  trailing nullcols
(
 c1,
 field2 boundfiller,
 field3 boundfiller,
 field4 boundfiller,
 field5 boundfiller,
 c2 ":field2 || :field3",
 c3 ":field4 + :field5"
)

oracle@mutation#cat somedata.data
12,Test,X,12,13
13,Test2,Y,14,16

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

SQL*Loader: Release 9.2.0.4.0 - Production on Thu May 4 09:33:14 2006

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

Commit point reached - logical record count 2

oracle@mutation#query mutation scott.sometable

        C1 C2                 C3
---------- ---------- ----------
        12 TestX              25
        13 Test2Y             30

Report message to a moderator

Previous Topic:	LogMiner fatal trouble (BLOB)
Next Topic:	PB in export and query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 08:08:34 CDT 2024