When in doubt, consult "Ask Tom" first!! I spent hours trying to figure out what I found on your site in less than 5 minutes! Search is powerful and answer I was looking for was right on the money.
Thanks Tom!!
Thank god...
john, February 04, 2003 - 4:39 pm UTC
I guess this was in the concepts guide somewhere, but beats me if I remember ever having seen it...
Thank god Tom addressed this as it had been driving me crazy...
Field...exceeds maximum length for a "filler" field.
news0reader, November 02, 2004 - 7:57 pm UTC
Tom, I'm using sql loader and I get the "exceeds maximum length" message for a field I've specified as a "filler" [Oracle 9i]. The data file description is as follows:
[sample.txt] col0 = number col1 = text [up to 10000 chars] col2 = number
My table has the following form: col0 = number col1 = number
My control file looks like this:
OPTIONS [ SKIP=1 ] LOAD DATA INFILE sample.txt "str X'02'" insert
INTO TABLE foo fields terminated by X'01' trailing nullcols [ aid , bio filler , b_id ]
And I get many records rejected with message:
\>>> Record 13: Rejected - Error on table foo, column BIO. Field in data file exceeds maximum length | Running the sql loader like this
sqlldr control=testlob_loader.ctl LOG=testlob_loader.log Getting Log like this
Table TESTLOB_LOADER, loaded from every logical record. Insert option in effect for this table: APPEND Column Name Position Len Term Encl Datatype
NO FIRST * , CHARACTER REMARKS NEXT 4000 , O[] value used for ROWS parameter changed from 64 to 60 Record 1: Rejected - Error on table TESTLOB_LOADER, column REMARKS. no terminator found after TERMINATED and ENCLOSED field Table TESTLOB_LOADER: 0 Rows successfully loaded. 1 Row not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 255600 bytes[60 rows] Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 1 Total logical records rejected: 1 Total logical records discarded: 0
July 02, 2012 - 7:15 am UTC
use:
INFILE demo.dat "str X'7C0A'"
\n is not meaningful to us.
A reader, July 02, 2012 - 10:39 am UTC
Hi Tom,
Still not working after changing the Str with Hex value as provided. Please see the log. It supposed to be 2 records as per the data file. Not sure why only 1 read. Even that too rejected. But when I see .bad file it shows both the records. Looks like everything is read as one record. I have to keep the data in .dat file and can not use LOB File for loading this CLOB data also. So please help what is wrong here. Is it due to the encoding of the file? Otherwise any error record end indicator would work better?
Total logical records skipped: 0 Total logical records read: 1 Total logical records rejected: 1 Total logical records discarded: 0
July 02, 2012 - 1:49 pm UTC
worked dandy for me, make sure you have a newline on the end of the last line [else it didn't end of |\n!]
ops$tkyte%ORA10GR2> desc t Name Null? Type ---------------------------------------- -------- ---------------------------- NO NUMBER REMARKS CLOB ops$tkyte%ORA10GR2> !cat t.ctl LOAD DATA INFILE test.dat "str X'7C0A'" replace into table T FIELDS TERMINATED BY ',' [ no, remarks CHAR[12000] optionally ENCLOSED BY '' ] ops$tkyte%ORA10GR2> !cat test.dat 1,| 2,CHAR[4000] NULLIF NOTE=BLANKS it works fine nowAnuradha, December 26, 2012 - 6:10 am UTC
Hi Tom,
I have a table with 3 fields. I am trying to load it with a data file which has 4 fields.
Is it possible to skip the first field from data file and load rest 3 inside table?
January 04, 2013 - 10:57 am UTC
see the filler keyword for sqlldr [assuming you are using sqlldr]
resolving the issues instantly & saving time
krishnareddynv, August 25, 2013 - 7:17 am UTC
Dear All, You all are REAL TEACHERS for providing immediate solutions. Thanking You for saving time and helping us to resolve the many issues in time correctly.
Meta, January 17, 2014 - 12:20 pm UTC
Thanks Tom, saved my day.
Question/problem
Dhruv, January 21, 2014 - 8:52 am UTC
Hi Tom,
Trying to load data from .dat file into table. The table has only one column [str1]. The .dat file contains rows and i need to load each of these rows into this column. The rows are 2 to 3 lines long.
I am getting the below error: SQL LOADER - Error Field in data file exceeds maximum length
Control file: load data infile 'ajay.dat' insert into table bbc.ajay1.bk2 trailing nullcols [ str1 char[4000]]
Thanks
A Thankful user, February 16, 2015 - 10:44 am UTC
Hi Tom,
I just wanted to thank you. Can always find the right to the point answers from your site.
Regards,
Great !
Suddhasatwa, June 22, 2015 - 11:58 am UTC
Thanks for the solution .. provided in 2004 for Oracle 9i, and works flawlessly in 2015 with 11gR2!
In multi-byte character sets, do we need to consider data_length or char_length
Moorthy Rekapalli, October 02, 2015 - 8:40 pm UTC
Tom,
Thank you very much for your help to Oracle community through this forum.
I have a follow-up question. In the following example, for columns that have char semantics and longer than 255, do we need to use data_length or char_length in sqlldr control files?
create table test[desc_byte varchar2[400 byte], char_desc varchar2[400 char]]; column column_name format a15 select column_name, data_length, char_length, char_col_decl_length, default_length from user_tab_columns where table_name = 'TEST' COLUMN_NAME DATA_LENGTH CHAR_LENGTH CHAR_COL_DECL_LENGTH DEFAULT_LENGTH
DESC_BYTE 400 400 400 CHAR_DESC 1600 400 1600
Thanks, Moorthy.
October 03, 2015 - 2:12 am UTC
From the documentation:
"The lengths are always interpreted in bytes, even if character-length semantics are in effect for the file. This is necessary because the file could contain a mix of fields, some processed with character-length semantics and others processed with byte-length semantics"
Just confirming on data_length vs. char_length
Moorthy Rekapalli, October 05, 2015 - 3:58 pm UTC
Tom,
Thank you for your prompt reply.
For char_desc column, do we specify char[1600] or char[400] in sqlldr control file?
Based on your response, thinking that it is data_length in bytes, which is char[1600]. But, just wanted to confirm.
Thanks, Moorthy.
October 05, 2015 - 11:53 pm UTC
yes, bytes
Quiet a useful Tip
Amish Parikh, October 13, 2015 - 12:22 pm UTC
Being new to Data Migration, such tips are really effort and time savings.Had spent cpuple of hours for the same.
This is exactly what I needed
Anne, December 11, 2015 - 4:20 pm UTC
Thanks so much for the 'tip' about specifying the char[1000] in the .ctl file. This solved my problem.
This article is STILL helping people!
Cathy Joyner, August 18, 2016 - 8:26 pm UTC
It's remarkable... this issue was originally posted in 2001. I've spent the last two days fighting with this problem and finally resorted to Ask Tom. It fixed my problem in 5 minutes. WHY did I not start here? I should know better. At any rate, fifteen years after the original post, I wanted to say THANK YOU! You're STILL helping people every day with your excellent information!! :]
August 18, 2016 - 11:11 pm UTC
Thank you for taking the time to give us feedback
great catch
Gaylon, September 29, 2016 - 1:02 pm UTC
Never knew about the 255 char default. Handy to know...
A reader, January 17, 2018 - 3:44 pm UTC
TOM is always rocking
Shan, January 29, 2018 - 5:55 pm UTC
Your solutions are really help me to resolve my issues.
January 30, 2018 - 2:35 am UTC
glad we could help
Unknown length of CLOB data
A reader, April 24, 2018 - 12:37 pm UTC
What if I don't know how big my CLOB data will be.
What syntax do I use to SQL*Load in an unknown length of character data terminated by ',' into a CLOB? Not a BFILE, but stored in the input data file.
April 26, 2018 - 10:48 am UTC
You would need to choose an appropriate upper bound.
related q
Colin de Silva, June 29, 2018 - 9:15 am UTC
Hi there, I have a related question. Is there an option for sqlldr to truncate a field if it is outside the bounds specified, rather than abort the record [and abandon the job if aborted records > x]?
cheers, Colin.
July 05, 2018 - 4:23 pm UTC
You can call functions to assign values to the columns. So you can substr the input to limit it:
SQL> create table t [ 2 id int, 3 c1 varchar2[2] 4 ]; Table T created. SQL> SQL> ho type sqlldr.ctl load data infile into table t fields terminated by "," [ id, c1 "substr[:c1, 1, 2]" ] begindata 1,x 2,xx 3,xxx 4,xxxx SQL> SQL> ho sqlldr userid=chris/chris@db SQL*Loader: Release 12.2.0.1.0 - Production on Thu Jul 5 17:21:56 2018 Copyright [c] 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 4 Table T: 4 Rows successfully loaded. Check the log file: sqlldr.log for more information about the load. SQL> SQL> select from t; ID C1 1 x 2 xx 3 xx 4 xx
Substr not worked in ctl file
Mahesh Rai, October 05, 2019 - 8:05 am UTC
Char[10000] "substr[:col1,1,4000]"
This is not working to cut 4k from 10k length string.
October 07, 2019 - 12:56 am UTC
Wow... one star. No test case, no error messages, just a one line saying "not worked", and what's more, I can prove you didn't test it, because it works!!!!
test.dat 1,my test remarks column sdkfjasdfkjsdfkjs....9000 more chars table SQL> create table t [ pk int, x varchar2[4000]]; Table created. control file LOAD DATA INFILE "c:\temp\test.dat" replace into table T FIELDS TERMINATED BY ',' [ pk, x CHAR[12000] "substr[:x, 1, 4000]" ] execution X:\>sqlldr control=c:\temp\test.ctl userid=/@db19_pdb1 SQL*Loader: Release 19.0.0.0.0 - Production on Mon Oct 7 08:53:30 2019 Version 19.3.0.0.0 Copyright [c] 1982, 2019, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 1 Table T: 1 Row successfully loaded. Check the log file: test.log for more information about the load. result -- SQL> select length[x] from t; LENGTH[X]
4000
A reader, December 04, 2020 - 6:13 am UTC
LOVED IT
Hassan Shahzad, April 22, 2021 - 6:50 pm UTC
Thank you so much. It did solve my problem. The command i wrote in my control file was:
LOAD DATA INFILE * INTO TABLE dummy3 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS [ cast char[4000], country, genre, description char[4000] ]
April 27, 2021 - 5:56 am UTC
Glad it worked out
ORA-01461: can bind a LONG value only for insert into a LONG column even with CLOB and FILLER
Shobhit Mittal, September 13, 2021 - 3:34 pm UTC
First of all, just an update that the code posted on 'October 07, 2019 - 12:56 am UTC' does not work as It throws the error ORA-01461: can bind a LONG value only for insert into a LONG column.
I started working on fixing this issue using CLOB and FILLER to skip loading the data in column.
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
September 14, 2021 - 1:04 pm UTC
Sorry, I'm unable to reproduce this.
Please provide a complete test case [create table + sqlldr control file] showing how you hit this problem
Great
Tiru, March 23, 2023 - 12:06 pm UTC
Thank you Tom, it helped me a lot.
Trying to import data in to dev database through sql developer got the below error.
Lakshmi, September 01, 2023 - 7:35 am UTC
File C:\Users\Rsurasi\Desktop\WC_MP_ELIG_HB_PP3.xlsx cannot be opened due to the following error: Tried to allocate an array of length 173, 101,780, but the maximum length for this record type is 100,000,000. If the file is not corrupt and not large, please open an issue on bugzilla to request increasing the maximum allowable size for this record type. Delim Left E You can set a higher override value with IOUtils.setByteArrayMaxOverride[].
Sql Developer version is 3.2.20.09.87
Please tell me the solution for this. Need to increase JVM Heap size in sqldeveloper.conf file or need to try in other format CSV ? will this fix the issue?
September 01, 2023 - 12:56 pm UTC
Bugzilla?! If you spot issues then you need to raise them with Oracle support: //support.oracle.com