| CONCAT fails in SQL*Loader [message #445571] |
Tue, 02 March 2010 10:53  |
9000233309
Messages: 1
Registered: March 2010
Location: Bentonville AR
|
Junior Member |
|
|
I'm trying to concatenate a local phone number field. The LDAP system only has the last 5 digits but for the directory database we need all 7 digits.
I've tried every combination I can think of to get the concatenation to work but every combination results in just the first two digits being imported, e.g.,
LOCAL_NUM "'20'||:local_num",
results in just 20 being imported. Every iteration I've tried that didn't result in an error imported only the 20 and ignored the ||. I've also tried calling the CONCAT directly, e.g.,
LOCAL_NUM "CONCAT('20', :local_num)",
result is the same.
The problem seems to be that the loader is ignoring the concatenate statement all together. I've tried the statements outside of the loader via sqlplus with expected result so I'm confused as to why it's not working within the loader.
Any ideas or suggestions would be appreciated.
|
|
|
|
|
|
| Re: CONCAT fails in SQL*Loader [message #445587 is a reply to message #445571] |
Tue, 02 March 2010 14:59  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Works fine for me on 10g XE.
Create a test table:SQL> create table test (local_num varchar2(7));
Table created.
Control file:load data
infile *
replace
into table test
fields terminated by whitespace
(local_num "'20' || :local_num")
begindata
12345
61223
98765
Loading session:SQL> $sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Uto O₧u 2 21:58:51 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
Commit point reached - logical record count 3
The result:SQL> select * from test;
LOCAL_N
-------
2012345
2061223
2098765
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
