Thanks for the question, Lei.

Asked: December 08, 2001 - 5:01 pm UTC

Last updated: July 02, 2021 - 10:55 am UTC

Version: SQL*Loader 8.1.7

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I tried to load a text file to Oracle db using SQL*Loader. Here is the table definition:
create table mytable (
key varchar2(40),
adate date,
atag number(10),
aclob clob,
version number(10)
);

Here is the SQL*Loader control file (actually it is generated by Oracle Migration Workbench when I converted it from Informix):

load data
infile 'mydata.dat' "str '<endrec>'"
into table mytable
trailing nullcols
( key char(40),
adate date 'DD/MM/YYYY',
atag ,
aclob char(1024000) nullif aclob=BLANKS,
version )

Since some rows may be big (because of CLOB column), SQL*Loader failed with this error:

SQL*Loader-510: Physical record in data file (mydata.dat) is longer than the maximum(1048576)

I tried to increase aclob size in control file, and tried to use BINSIZE on command line, but none of them worked.

My question is: does SQL*Loader have a limit on the size of physical record? How can load a large record into Oracle DB?

I am using Oracle 8.1.7 on Solaris 2.7.

Thank you very much.

- Lei

and Tom said...

Use:

readsize=3000000

or some large number to raise this limit. (i just did a 2meg clob using that)

Rating

(10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, it worked.

Lei, December 10, 2001 - 10:04 am UTC

Thank you very much.

The size limit on CLOB is 4GB. Is there a limit on readsize? May I set readsize=4GB so that it may work on all CLOBs?

Thanks.

- Lei

December 10, 2001 - 1:55 pm UTC

looks like readsize is a signed integer, 2billion is the max HOWEVER, you'll probably get:

$ sqlldr / t readsize=2000000000 bindsize=2000000000

SQL*Loader: Release 8.1.5.0.0 - Production on Mon Dec 10 13:54:54 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.

SQL*Loader-700: Out of memory while performing essential allocations [7]

so, no, maxing it out would not be feasible.

reader, December 10, 2001 - 3:14 pm UTC

What si sql*loader -524 error and how to solve it
I tried oerr utility but I don't know what prefix should i use with it ?

like oerr ora -524 OR oerr ldr-524 ?

December 10, 2001 - 3:55 pm UTC

SQL*Loader-524: partial record found at end of datafile;

that means the last record in the input datafile did not have the end or record marker.

search for

-524

on this site for details.

How to automatically generate the control file?

Don, March 31, 2003 - 4:36 pm UTC

Do you know of a oracle utility or have any script which can generate the control file for the table in your example i.e mytable (with a clob column)

March 31, 2003 - 5:02 pm UTC

not with a clob per-say but see

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

sqlldr_exp does it -- you can modify that to suit your needs.

Nicely done ...

A reader, April 01, 2003 - 12:49 pm UTC

sql*loader-510 physical record in data file

Dumitru Sarateanu, January 22, 2018 - 10:54 am UTC

hi. Can you help me. I need to upload in oracle database a .ctl file using sqlldr.exe. And I receive error sql*loader-510 physical record in data file. I increased readsize=3000000. And same issue. And when I increse more readsize=300000000000, it's sql*loader-100 syntax error on command-line.
I'm inserting in cmd: "C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr.exe Database control=c:\temp\zoneName.ctl readsize=3000000".

Could you help me, please.

January 23, 2018 - 1:58 am UTC

Did you try bindsize as well ?

And exactly what are you trying to load ? More details needed

sql*loader-510

Dumitru Sarateanu, January 23, 2018 - 5:56 am UTC

Hi. Yes I did, and I have the same issue. And I'm inserting in cmd: "C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr.exe Database control=c:\temp\zoneT.ctl readsize=3000000";
"C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr.exe Database control=c:\temp\zoneT.ctl bindsize=3000000";
"C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr.exe Database control=c:\temp\zoneT.ctl readsize=3000000 bindsize=3000000";
My record is longer then tha maximum <65536>.

January 23, 2018 - 1:19 pm UTC

So you have a row that is more than 64k long ?

You might be better off putting that large data into a file and loading into a clob column

sql*loader-510 physical record in data file

Dumitru Sarateanu, January 24, 2018 - 7:36 am UTC

Hi. Yes in ctl file I have a row biger than 64k long. It's geographical cordonates.

January 25, 2018 - 2:55 am UTC

Yeah, I think you are out of luck.

If you can take the lob data and split it over multiple lines, then we can still load it as one object via the techniques described here

https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_loading.htm#SUTIL1264

sql*loader-510 physical record in data file

Dumitru Sarateanu, January 25, 2018 - 7:38 am UTC

Hi. Sorry, I'm beginner in Oracle DB. Can I contact you on mail and send you my .ctl file and if you could show me an example. Thank you very much for your understanding and patience.

Data loading

A reader, March 10, 2020 - 10:24 am UTC

It Work's. Thank you

I solved this way

EVERTON PAULA BISPO, July 01, 2021 - 8:27 pm UTC

Hi guys,

I had the same problems. I fixed this issue by recreating the table including the readsize parameter inside the definition as follows:

create table test_table (
description varchar2(100)
)
organization external
(
type ORACLE_LOADER
default directory <<directory>>
access parameters
(
records delimited by newline readsize 67108864 SKIP 1 fields terminated by ';' missing field values are null
)
location (<<directory>>:'myFile.csv')
)
reject limit UNLIMITED;

July 02, 2021 - 10:55 am UTC

Thanks for sharing

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating