?
Solved

sql loader control file

Posted on 2010-11-18
10
Medium Priority
?
1,035 Views
Last Modified: 2013-12-08
I  am loading data from a csv file, and I am getting customer_id value from csv file, I want to insert the same customer_id into 3columns. 2columns are in 1 table and another column is in another column. The same thing in the form of query is

Insert into to2_user (customer_id, login, password) select customer_id,customer_id,customer_id from dps_user.

The control file is not allowing me to reference customer_id for multiple places because it has to read the data from the beginning of the row.

How to mention this in control file. Please help.
0
Comment
Question by:Ravi_Chintada
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
10 Comments
 
LVL 8

Accepted Solution

by:
Zlatin Zlatev earned 1000 total points
ID: 35322839
See SQL Loader documentation at http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch06.htm
section "Using POSITION with Multiple Table Loads"

When you specify POSITION() for the first column of the first table, the position is calculated relative to the beginning of the logical record (which allows you to reference the customer_id value twice)

P.S. This should be in the Oracle forum btw.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 35326344
Given the following table and control file (I used inline data for test purposes):

I used:
sqlldr username/password control=myControl.ctl

I end up with
SQL> select * from tab1;
a a

1 row selected.

SQL> select * from tab2;
a

1 row selected.

drop table tab1 purge;
create table tab1 (col1 char(1), col2 char(1));

drop table tab2 purge;
create table tab2 (col1 char(1));

Open in new window

load data
 infile *
 truncate INTO TABLE Tab1
 FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
col1,
col2 ":col1"
)
 INTO TABLE Tab2
 FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
col1 position(1)
)
begindata
a|b

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35326404
Ravi_Chintada,

The reason you never received a response to this is it is strictly Oracle related and was not asked in an Oracle Zone.

The Experts in the initial zones should have let you know this so it could have been addresses long before now.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35338506
Can you provide your sample data in the data file for few records so it becomes easy to come up with a control file for sql loader ?

Thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35338549
Is extra data necessary?  I took data, added it to two columns in a single table and another column in a different table.

Seems to meet the requirements.
0
 
LVL 8

Expert Comment

by:Zlatin Zlatev
ID: 35767930
I am not sure if the author needs more assistance with this one.
Still I think my comment has pointed him in the right direction, so it is a helpful comment, even if it may not be the complete solution to the problem.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35768692
I tend to agree and would suggest a split between:
http:#a35322839
http:#a35326344
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article offers some helpful and general tips for safe browsing and online shopping. It offers simple and manageable procedures that help to ensure the safety of one's personal information and the security of any devices.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This Micro Tutorial will demonstrate how to add subdomains to your content reports. This can be very importing in having a site with multiple subdomains.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question