Solved

sql loader control file

Posted on 2010-11-18
10
987 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
  • 4
  • 2
10 Comments
 
LVL 8

Accepted Solution

by:
zlatev earned 250 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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: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 76

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

785 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