Home » RDBMS Server » Server Utilities » How to write a control file to concatenate or add fileds (sql Loader)
How to write a control file to concatenate or add fileds (sql Loader) [message #170273] |
Wed, 03 May 2006 01:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
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
|
|
|
Goto Forum:
Current Time: Thu Aug 29 22:22:30 CDT 2024
|