Problem Note 47089: "ERROR:SQL*Loader-510 Physical record in data file longer than the maximum" might occur when bulkloading data into Oracle
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
SAS System | SAS/ACCESS Interface to Oracle | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows XP 64-bit Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft® Windows® for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2003 for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2008 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows Server 2008 for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Microsoft Windows XP Professional | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Enterprise 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Enterprise x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Home Premium 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Home Premium x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Professional 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Professional x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Ultimate 32 bit | 9.2 TS2M3 | 9.2 TS2M3 |
Windows 7 Ultimate x64 | 9.2 TS2M3 | 9.2 TS2M3 |
Windows Vista | 9.2 TS2M3 | 9.2 TS2M3 |
Windows Vista for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
64-bit Enabled AIX | 9.2 TS2M3 | 9.2 TS2M3 |
64-bit Enabled HP-UX | 9.2 TS2M3 | 9.2 TS2M3 |
64-bit Enabled Solaris | 9.2 TS2M3 | 9.2 TS2M3 |
HP-UX IPF | 9.2 TS2M3 | 9.2 TS2M3 |
Linux | 9.2 TS2M3 | 9.2 TS2M3 |
Linux for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
OpenVMS on HP Integrity | 9.2 TS2M3 | 9.2 TS2M3 |
Solaris for x64 | 9.2 TS2M3 | 9.2 TS2M3 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
SQL*Loader-510: Physical record in data file (
) is longer than the maximum(2147473647) occurs when bulkloading a SAS® table with a large number of columns into Oracle.
Type: | Problem Note |
Priority: | high |
Date Modified: | 2012-08-08 07:49:41 |
Date Created: | 2012-07-30 07:46:19 |