Home » RDBMS Server » Server Utilities » Field concatenation during load using SQL*Loader (SQL*Loader: Release 9.2.0.1.0)
icon5.gif  Field concatenation during load using SQL*Loader [message #328603] Fri, 20 June 2008 14:14 Go to next message
kwaam_rak
Messages: 14
Registered: July 2007
Junior Member
I want to use SQL*Loader to load a CSV file into a table. It is not straight forward though. There is a minor data transformation to be done. Here is the scenario:

Table has 2 fields: emp_id, emp_addr

Data to be loaded has 3 columns: emp_id, emp_city, emp_state

I want to concatenate emp_city and emp_state to be inserted to emp_addr.

Control file:

LOAD DATA
INTO TABLE emp
APPEND
FIELDS TERMINATED BY ','
(emp_id,
 emp_city FILLER,
 emp_addr ":emp_city||' '||:emp_addr")


I get this error:

Quote:
SQL*Loader-291: Invalid bind variable EMP_CITY in SQL string for column EMP_ADDR.


Any thoughts on how to go about doing this using SQL*Loader? Is this even possible in SQL*Loader?

Re: Field concatenation during load using SQL*Loader [message #328609 is a reply to message #328603] Fri, 20 June 2008 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
This problem can be solved using only SQL (& external tables).
Re: Field concatenation during load using SQL*Loader [message #328616 is a reply to message #328609] Fri, 20 June 2008 16:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You can use BOUNDFILLER instead of FILLER, as demonstrated below.

-- contents of test.dat:
1,city1,state1,
2,city2,state2,


-- contents of test.ctl:
LOAD DATA
INTO TABLE emp_test
APPEND
FIELDS TERMINATED BY ','
(emp_id,
emp_city BOUNDFILLER,
emp_addr ":emp_city||' '||:emp_addr")


-- create table, load, and results:
SCOTT@orcl_11g> CREATE TABLE emp_test
  2    (emp_id	  NUMBER,
  3  	emp_addr  VARCHAR2 (20))
  4  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl DATA=test.dat LOG=test.log

SCOTT@orcl_11g> SELECT * FROM emp_test
  2  /

    EMP_ID EMP_ADDR
---------- --------------------
         1 city1 state1
         2 city2 state2

SCOTT@orcl_11g> 


However, I should point out that it is not a good design to combine the columns. It is better to have them separate. If you want to search for an individual city or state it is easier to do so if they are separate. It is easier to combine columns when needed than to accurately separate them.

icon14.gif  Re: Field concatenation during load using SQL*Loader [message #328938 is a reply to message #328616] Mon, 23 June 2008 08:14 Go to previous message
kwaam_rak
Messages: 14
Registered: July 2007
Junior Member
I tried BOUNDFILLER and it works! Thank you so much for the help!

Before posting this question, I was already considering loading into a temp table, and then doing some PL/SQL for the actual loading.

Now thanks to your suggestion, the loading process is made simple.

And yeah, I agree it is not a good design. It's a made-up scenario for simplicity. Smile
Previous Topic: Naming user trace files
Next Topic: Importing from Excel file into Oracle DB table
Goto Forum:
  


Current Time: Sat Dec 03 16:28:17 CST 2016

Total time taken to generate the page: 0.05428 seconds