When you bulkload a SAS^® table with a large number of columns into Oracle, the following error might occur:

ERROR: SQL*Loader-510: Physical record in data file (control file) is longer 
than the maximum(2147473647)

This error occurs when the line length in the Oracle bulk load control file exceeds 64K.

A possible workaround on UNIX platforms is to use a shell script that inserts line breaks into the control file before calling the actual Oracle SQL*Loader. To do this, create a script file with the following content:

ppid=$1
shift
sed < /tmp/$ppid.ctl > /tmp/$$.ctl -e 's/ ,/,\
/g'
mv /tmp/$$.ctl /tmp/$ppid.ctl
$ORACLE_HOME/bin/sqlldr $*

In this example, the script file is saved to /user/oracle/scripts/sqlldr_prefix. Add execution attributes to the script file:

$ chmod +x /user/oracle/scripts/sqlldr_prefix

From SAS, use the BL_CONTROL= and BL_SQLLDR_PATH= options with bulkload to run the script. Here is an example:

PROC APPEND 
     BASE=ORALIB.TABLE(
          BULKLOAD=YES
          BL_CONTROL="/tmp/&sysjobid..ctl"
          BL_SQLLDR_PATH="/user/oracle/scripts/sqlldr_prefix &sysjobid"
     )
     DATA=SASTABLE;
RUN;

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for SAS/ACCESS Interface to Oracle 9.21_M2 is available at: