Home » RDBMS Server » Server Utilities » Getting Field in data file exceeds maximum length when using sqlldr (sqlldr 10g )
Getting Field in data file exceeds maximum length when using sqlldr [message #439352] Fri, 15 January 2010 14:24 Go to next message
ole2009
Messages: 12
Registered: May 2009
Junior Member
Hi,

I try to upload a table that has a column with varchar2(4000).
In the control file, I specify the column size as char(4000) and I try to use substr function to restrict the data size.

However, I still get 'Field in data file exceeds maximum length' when the data size is more than 4000. It looks like Oracle validate the data size before executing substr function. Any work around there to upload this kind of data if the data size is large than the size defined in control file?

Thanks for your help,

Here is the control file:

LOAD DATA
APPEND
INTO TABLE big_table

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
col1,
col2 char(4000) "substr(:col2,1, 4000)"
)
Re: Getting Field in data file exceeds maximum length when using sqlldr [message #439368 is a reply to message #439352] Fri, 15 January 2010 22:16 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/wiki/SQL%2ALoader_FAQ#Can_one_load_variable_and_fixed_length_data_records.3F
Re: Getting Field in data file exceeds maximum length when using sqlldr [message #439369 is a reply to message #439368] Fri, 15 January 2010 22:25 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL LOADER - SUBSTRING problem

sriram Smile
Re: Getting Field in data file exceeds maximum length when using sqlldr [message #439805 is a reply to message #439352] Tue, 19 January 2010 08:35 Go to previous messageGo to next message
ole2009
Messages: 12
Registered: May 2009
Junior Member
I cannot use position due to data size is not fixed.
Re: Getting Field in data file exceeds maximum length when using sqlldr [message #439860 is a reply to message #439805] Tue, 19 January 2010 14:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
You can load it into a clob column without the substr, using char(5000) or however big it needs to be. Then, after loading, you can take the substr of the clob column.
Re: Getting Field in data file exceeds maximum length when using sqlldr [message #440034 is a reply to message #439860] Wed, 20 January 2010 13:36 Go to previous message
ole2009
Messages: 12
Registered: May 2009
Junior Member
It is exactly what I am doing.
Previous Topic: how to load data from an tsv into an oracle table w/o using SQLLDR?
Next Topic: Full Import
Goto Forum:
  


Current Time: Mon Dec 05 21:18:12 CST 2016

Total time taken to generate the page: 0.21588 seconds