RE: SQLLDR using Direct=True with Multiple Physical Records

From: Mark W. Farnham <>
Date: Sun, 27 Jan 2008 17:42:06 -0500
Message-ID: <00e601c86135$d8fab890$>, test table  

hmm - have you considered using sqlplus copy?  

if you have type long columns make sure you set long to at least the length of the longest long you have, set the product of copycommit and arraylength to something in the range 512 to 2048.  

Are you duplicating the data to a test table in the same database? If so, check if create table as select already exists as of that release.  

If you must produce the intervening flat file, others have mentioned using a longer linesize. I can't remember the maximum linesize as of 8.0, but I think it was still smallish. If you have a more modern release of sqlplus it can probably still be used against the 8.0 database, but depending on how you set up your path you might have to reference the newer copy more explicitly than just typing sqlplus.  

Remember also to check whether any of your columns contain the value ',' which not be compatible with using ',' as the field separator. The most unlikely character to find in character columns in Oracle databases, by the way, is the field separator character, so you might consider using that instead of ',' as the field separator.  

If your operating system or the sqlplus available limits you to flat files less wide than you need, you can calculate maximums as if some row filled each varchar and figure out where to place 'CRLF*' literals (or 'NEWLINE*') in the column list so you get more than two physical rows per logical row consistently. If you do that, you probably want to group the intended physical rows as columns, and again you need to verify that the characters do not appear in your data.  

The best way to skin this cat depends on many details of your exact situation. Probably several different ways will work. Trying to use direct=true implies you have some performance concern. Where the sweet spot is between avoiding producing the intervening flat file and the speed of direct=TRUE is a tough question to guess at, especially as far back as 8.0. I'd avoid parallel index creation on that release, by the way.  



From: [] 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


Tara Mason
Database Administrator

Received on Sun Jan 27 2008 - 16:42:06 CST

Original text of this message