[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

sql loader control file

Posted on 2010-11-18
10
Medium Priority
?
1,054 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

#Citrix #Internet Explorer #Enterprise Mode #IE 11 #IE 8
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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