RE: SQLLDR using Direct=True with Multiple Physical Records
Date: Fri, 25 Jan 2008 11:20:58 -0500
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_at_prod_db where <whatever>;
Hope that helps,
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_at_il.proquest.com<mailto:mark.bobak_at_il.proquest.com> www.proquest.com<http://www.proquest.com> www.csa.com<http://www.csa.com>
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mason, Tara
Sent: Friday, January 25, 2008 10:56 AM
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 184.108.40.206.0.
http://www.freelists.org/webpage/oracle-l Received on Fri Jan 25 2008 - 10:20:58 CST