SQLLDR using Direct=True with Multiple Physical Records
From: Mason, Tara <Tara.Mason_at_safety-kleen.com>
Date: Fri, 25 Jan 2008 09:55:34 -0600
Message-ID: <24251CE3F452494AAFC53CF0BA822C570246CD47@SKUSPLCEXCH1.corporate.sk.local>
Date: Fri, 25 Jan 2008 09:55:34 -0600
Message-ID: <24251CE3F452494AAFC53CF0BA822C570246CD47@SKUSPLCEXCH1.corporate.sk.local>
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_at_SAFETY-KLEEN.COM
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 25 2008 - 09:55:34 CST