Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> SQL-Loader question, Uploading DataFiles from FoxPro to Oracle via SQL-Loader
YankeeFan
post Feb 7 2012, 09:25 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 7-February 12
Member No.: 46,728



Greetings all
We are using Oracle 10g, on redhat linux (version not known to me).

I'm running a Perl script that reads in data from Visual FoxPro tables, creates datafiles on the fly from these FP tables, then SQL-Loader runs and uploads all the data to matching tables in Oracle.

Problem:
My upload runs, but I'm not getting the data in some of the fields that I should be. It is being uploaded to the wrong fields.

I had to add more columns to the Oracle tables in order to capture the data coming from FoxPro. These columns were/are in FoxPro. In the past, they were not needed in Oracle. I was using the FILLER command in the Control files to skip these. Now they are needed in Oracle. The new columns get added to the end of the table in Oracle, so they are not in the same exact order they are in FoxPro. It is these new columns that are not getting the correct data.

I've learned in the past that I need the fields/columns in the control files to match the order they are in the FoxPro tables from which the data comes.

My Question is, Does the Oracle table column order have to match the Control file column order. Like so:
FoxPro table column order = control file column order = Oracle table column order

Thanks for any tips.
YankeeFan

Go to the top of the page
 
+Quote Post
burleson
post Feb 7 2012, 02:45 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



Hi Yank,

>> I've learned in the past that I need the fields/columns in the control files to match the order they are in the FoxPro tables from which the data comes.

Just change the FoxPro SQL to extract the columns in the same order as the Oracle table!

CODE
select
b||','||c||','||a from foxtab;


***********************************************
>> creates datafiles on the fly from these FP tables

Using what character to delimit the columns?

Can you paste-in a sample?

*****************************************
>> It is being uploaded to the wrong fields.

It sounds like your SQLLDR parfile does not match the data, else your character delimiter is not unique:

Please read:

http://www.dba-oracle.com/tips_sqlldr_loader.htm

The Oracle Utilities book may also help you:

http://www.rampant-books.com/book_0801_oracle_utilities.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd November 2014 - 12:24 PM