Home » RDBMS Server » Server Utilities » concatenate string to column while loading
concatenate string to column while loading [message #163340] |
Thu, 16 March 2006 05:45 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Zakkhalid
Messages: 47 Registered: April 2005
|
Member |
|
|
I have data as so
virgin.net,1.1,Primary|PAYGSAW,"Pamela","Wild",M,01/31/2005,-,-,03/13/2006
virgin.net,29duthie.terrace,Primary|PAYGSAW,"-","-",M,07/26/2005,-,02/25/2006,10/11/2005
virgin.net,44.uk,Primary|PAYGSAW,"-","-",M,07/13/2005,07/13/2005,-,07/25/2005
I have a FILLER for the first column, but for second I want concatenate a string to a column whilst loading,
so 1.1 is loaded as @net.com
in the controlfile I've tried, another combinations of
domain FILLER
,mailbox1 "(:MAILBOX1)||'@net.com'"
but get loads of errors...
|
|
|
Re: concatenate string to column while loading [message #163343 is a reply to message #163340] |
Thu, 16 March 2006 05:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.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#cat dept.ctl
LOAD DATA
infile 'dept.data' truncate
INTO TABLE dept
FIELDS TERMINATED BY ","
trailing nullcols
(
deptno ,
dname ":dname||'X'",
loc
)
oracle@mutation#cat dept.data
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
oracle@mutation#sqlldr userid=scott/tiger control=dept.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Thu Mar 16 06:57:37 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
oracle@mutation#query mutation scott.dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTINGX NEW YORK
20 RESEARCHX DALLAS
30 SALESX CHICAGO
40 OPERATIONSX BOSTON
|
|
|
Re: concatenate string to column while loading [message #163348 is a reply to message #163340] |
Thu, 16 March 2006 06:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Zakkhalid
Messages: 47 Registered: April 2005
|
Member |
|
|
Hi
Thanks for prompt response
I applied you info to my script... it didn't work, so I took your example and tried to create your results..but I keep getting the following
SQL*Loader-297: Invalid syntax or bind variable in SQL string for column DNAME.
ORA-01756: quoted string not properly terminated
and by ctl file is the same as yours
LOAD DATA
infile 'x.csv' truncate
INTO TABLE dog
FIELDS TERMINATED BY ","
trailing nullcols
(
deptno ,
dname ":dname||'X'",
loc
)
[Updated on: Thu, 16 March 2006 06:54] by Moderator Report message to a moderator
|
|
|
Re: concatenate string to column while loading [message #163358 is a reply to message #163348] |
Thu, 16 March 2006 06:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Well, obviously it works for me.
Does your table DOG has the same columns like standard DEPT table?
And always apply formatting while posting the code. Else it will be very hard to understand.
I mean, my dept is defined as
Table:scott.dept
Name Null? Type
----------------------------------- -------- ------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
[Updated on: Thu, 16 March 2006 07:06] Report message to a moderator
|
|
|
|
|
|
Re: concatenate string to column while loading [message #163460 is a reply to message #163459] |
Thu, 16 March 2006 15:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Can you use Direct load ? then it works.
oracle@hemlock#cat dept.ctl
LOAD DATA
infile 'dept.data' truncate
INTO TABLE dept
FIELDS TERMINATED BY ","
trailing nullcols
(
deptno ,
dname ":dname||('X')",
loc
)
oracle@hemlock#cat dept.data
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
oracle@hemlock#sqlldr userid=scott/tiger control=dept.ctl direct=y
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 16 16:39:36 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 4.
oracle@hemlock#./q_dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTINGX NEW YORK
20 RESEARCHX DALLAS
30 SALESX CHICAGO
40 OPERATIONSX BOSTON
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 05:56:10 CDT 2024
|