SQLLDR using Direct=True with Multiple Physical Records

  • From: "Mason, Tara" <Tara.Mason@xxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 25 Jan 2008 09:55:34 -0600

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

*        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

This is in Oracle


Tara Mason
Database Administrator


Other related posts: