SQL-Loader question, Uploading DataFiles from FoxPro to Oracle via SQL-Loader Options

[YankeeFan]

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

[burleson]

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