RE: SQLLDR using Direct=True with Multiple Physical Records

Hi Tara,

My first thought is, "Don't use SQL*Loader."  Might I suggest that there are 
better and more efficient ways to move the data than SQL*Loader.  SQL*Loader is 
great for moving data from a flat file into the database.  It's less than 
optimal for database to database moves, mostly cause it required the 
intermediate step of writing to a flat file.  If you're going to dump data from 
the database to a file and then read into another database, you're better off 
using exp/imp or expdp/impdp.

However, in terms of solving the problem, my first thought is, Insert/select 
across a database link.  Connect to test instance, create a database link to 
prod, then do insert /*+ append */ into table select * from table@prod_db where 
<whatever>;

Hope that helps,

-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxxxxx<mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com<http://www.proquest.com>
www.csa.com<http://www.csa.com>

ProQuest...Start here.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Mason, Tara
Sent: Friday, January 25, 2008 10:56 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: SQLLDR using Direct=True with Multiple Physical Records


I need to move data from a production table to the test table.  I would like to 
use sqlldr with DIRECT=TRUE.  The problem I have is there are multiple physical 
records on the file for one row on the table.  To retrieve the data from the 
production side I execute the following SQL statement:  SELECT COL1 || ','|| 
COL2 || ',' ||, etc.  There are many varchar columns on this table; therefore, 
the number of physical lines can vary.  The following are some scenarios I have 
tried along with the results:

*         Added an "*" to the first position and tried using the "continueif 
this (1) = "*" " clause, but this seems to only work if there are only 2 
physical records per row.

*         Tried selecting the varchar columns using the rpad function.  This 
allowed a consistent number of physical records.  The problem came when trying 
to run the sqlldr with direct=true utilizing the ltrim function.  In this old 
version of Oracle you cannot use SQL processing.  I received the SQL*Loader-417 
error.

Do you have any suggestions on how to load this data using sqlldr with 
direct=true?

This is in Oracle 8.0.5.0.0.

Thanks,
Tara Mason
Database Administrator
TARA.MASON@xxxxxxxxxxxxxxxx

Other related posts: