Home » RDBMS Server » Server Utilities » CONCAT fails in SQL*Loader (Oracle 10G)
CONCAT fails in SQL*Loader [message #445571] Tue, 02 March 2010 10:53 Go to next message
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 #445576 is a reply to message #445571] Tue, 02 March 2010 11:50 Go to previous messageGo to next message
Messages: 24963
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Please realize we ONLY know what you post.

We don't know what data or format you are attempting to load.
We don't know control file.
We don't know command line you do.
We don't know into what DB the data is supposed to be loaded.
We don't know how LDAP relates to any of above.

We will stipulate a problem exists, but you need to help us help you.
Re: CONCAT fails in SQL*Loader [message #445587 is a reply to message #445571] Tue, 02 March 2010 14:59 Go to previous message
Messages: 20847
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 *

into table test
  fields terminated by whitespace
  (local_num "'20' || :local_num")


Loading session:
SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release - 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;


Previous Topic: importing from .dmp file taken from different edition
Next Topic: Dump Table from One Schema to Other schema
Goto Forum:

Current Time: Tue Oct 25 21:43:38 CDT 2016

Total time taken to generate the page: 0.23686 seconds