 Field concatenation during load using SQL*Loader [message #328603] |
Fri, 20 June 2008 14:14  |
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 #328616 is a reply to message #328609] |
Fri, 20 June 2008 16:04  |
 |
Barbara Boehmer
Messages: 9101
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
