Skip to Main Content
  • Questions
  • SQLLDR fails on data specified as filler in control file

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Terry.

Asked: February 14, 2017 - 12:50 pm UTC

Last updated: September 14, 2021 - 3:40 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

I have a sqlldr control file that looks like this

name char(255) enclosed by '"',
contactemail char(255) enclosed by '"',
rawtext1 filler enclosed by '"',
rawtext2 filler enclosed by '"',
auditdate char(255) enclosed by '"'

my csv file that contains the data each field is enclosed in quotes and comma separated.
when I run sqlldr the load errors out and the log file contains the following:

Record ###: Rejected - Error on table Test, column rawtext1.
Field in data file exceeds maximum length.

I have tried creating the Test table with Varchar2(4000) length columns for the rawtext columns and I have tried creating them as clobs. Neither one makes any difference.

What an I doing wrong?

Terry

and we said...

Unless specified in a control file, string fields in the *controlfile* are assumed to be length 255. We dont automatically link fields to their matching database columns.

So

col enclosed by '""

is equivalent to:

col char(255) enclosed by '""

which will reject the row if its longer than 255. So just add (say) "char(2000)" in the controlfile where appropriate.

Rating

  (3 ratings)

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

Comments

Terry Jensen, February 15, 2017 - 11:17 am UTC

I have tried using char(4000) in the control file for the rawtext fields and still I get the same error. I thought that using the filler keyword would cause sqlldr to just skip the entire field no matter how long it is, because I don't want to load that data at all.

So I guess my next question is how do I skip loading a field that is larger that 4000 characters?
Connor McDonald
February 16, 2017 - 4:03 am UTC

Hmmm.... this works for me:

Load DATA
INFILE *
REPLACE
INTO TABLE T 
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
name char(255) enclosed by '"', 
contactemail char(255) enclosed by '"', 
rawtext1  filler char(20000) enclosed by '"', 
rawtext2  filler char(20000) enclosed by '"', 
auditdate char(255) enclosed by '"' 
)
BEGINDATA
"name1","email1","[15000 byte string]","rawdata2","audit1"
"name2","email2","[15000 byte string]","rawdata2","audit2"

SQL> select * from t;

NAME
-------------------------------------
CONTACTEMAIL
-------------------------------------
AUDITDATE
-------------------------------------
name1
email1
audit1

name2
email2
audit2



where the [15000 byte string] was as the name suggests... a long line of junk

Terry Jensen, February 16, 2017 - 10:06 am UTC

That worked brilliantly. I did not know that you could specify a value greater that 4000 in a char so I never tried anything higher. Thanks so much.
Connor McDonald
February 17, 2017 - 12:41 am UTC

Glad we could help

ORA-01461: can bind a LONG value only for insert into a LONG column - With Clob and FILLER

Shobhit Mittal, September 13, 2021 - 3:11 pm UTC

I am working on a CTL file which expects one of the column to have more than 16000 characters which we want to skip from loading to table.

Table: XXAP_CHR_EXP_LINE has column XXAP_CHR_EXP_LINE as CLOB

CTL: XXAP_CHR_EXP_LINE FILLER CHAR(20000) OPTIONALLY ENCLOSED BY '"'
which is interpreted as:
EXPENSE_LINE_ITEM_GUESTS NEXT 20000 | O(") CHARACTER
(FILLER FIELD)

When we run this with a data having data column around 15000 chars then also it is failing saying 'ORA-01461: can bind a LONG value only for insert into a LONG column'.

I also checked with 10000 chars then it works fine. Not sure why it's not working with CLOB. Even if I change the CHAR length in CTL more than 20000 say 50000 then also it's not working.

Regards,
Shobhit
Chris Saxon
September 14, 2021 - 3:40 pm UTC

As requested in this follow-up, please provide a complete test case showing this issue

https://asktom.oracle.com/pls/apex/asktom.search?tag=sqlloader-error-field-in-data-file-exceeds-maximum-length

And please - only ask a question in one place!

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here