OraFAQ Forum: Server Utilities » concatenate string to column while loading

Home » RDBMS Server » Server Utilities » concatenate string to column while loading

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

concatenate string to column while loading [message #163340]

Thu, 16 March 2006 05:45

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...

Report message to a moderator

Re: concatenate string to column while loading [message #163343 is a reply to message #163340]

Thu, 16 March 2006 05:57

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

Report message to a moderator

Re: concatenate string to column while loading [message #163348 is a reply to message #163340]

Thu, 16 March 2006 06:28

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

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 #163360 is a reply to message #163358]

Thu, 16 March 2006 07:15

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

Senior Member
Account Moderator

Also please post your version/table DDL

Report message to a moderator

Re: concatenate string to column while loading [message #163439 is a reply to message #163358]

Thu, 16 March 2006 14:01

Zakkhalid
Messages: 47
Registered: April 2005

Member

OK this I don't understand I just done it all again on an 9i db and your code worked fine... but it keeps failing on my 10g db; am I missing some env setting?

I'll post the env soon, as my 10g db server has just suffered a disk failure as I'm writing this update Sad

Report message to a moderator

Re: concatenate string to column while loading [message #163459 is a reply to message #163439]

Thu, 16 March 2006 15:48

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

Senior Member
Account Moderator

Known bug in 10g.
I will keep you posted.
I need know how to concatentate a constant.
Will keep you posted.

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

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

Report message to a moderator