Home » RDBMS Server » Server Utilities » SQLLDR Error : Field in data file exceeds maximum length
icon14.gif  SQLLDR Error : Field in data file exceeds maximum length [message #278901] Tue, 06 November 2007 04:19 Go to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Hi,

I'm getting the Field in data file exceeds maximum length. Is there some limit that I'm unaware of??? The column it is failing on is VARCHAR2(4000). The data file is pipe delimited and the file it is failing on is only 260 characters???

I'm confused on why I'm getting this error... has anyone else had this issue before or know a work around.

Below is the details regarding my table, load and data.


Table Layout
CREATE TABLE FR_CAT5_CAPTION_REF ( 
  REPORT_SHORT_DESC_TX    VARCHAR2(20)   NOT NULL , 
  REPORT_CAPTION_DESC_TX  VARCHAR2(100)  NOT NULL , 
  ACTUAL_CALC_TX          VARCHAR2(4000)  NOT NULL ,
  BUDGET_CALC_TX          VARCHAR2(4000)  NOT NULL ,
  VARIANCE_CALC_TX        VARCHAR2(4000)  NOT NULL ,
  CONSTRAINT FR_CAT5_CAPTION_REF_PK PRIMARY KEY (REPORT_SHORT_DESC_TX,REPORT_CAPTION_DESC_TX)
)

Table Load Script
OPTIONS (DIRECT=FALSE,SKIP=1,BINDSIZE=2500000)
LOAD DATA
INFILE '$download/$file_name.dat'
BADFILE '$bad_data/$file_name.bad'
DISCARDFILE '$bad_data/$file_name.dsc'
TRUNCATE
INTO TABLE IO.FR_CAT5_CAPTION_REF
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
-- Identified Columns --
  (   REPORT_SHORT_DESC_TX     "TRIM(:REPORT_SHORT_DESC_TX)"
  ,   REPORT_CAPTION_DESC_TX   "TRIM(:REPORT_CAPTION_DESC_TX)"
  ,   ACTUAL_CALC_TX           "TRIM(:ACTUAL_CALC_TX)"
  ,   BUDGET_CALC_TX           "TRIM(:BUDGET_CALC_TX)"
  ,   VARIANCE_CALC_TX         "TRIM(:VARIANCE_CALC_TX)"
  )


ERROR MSG
Record 1: Rejected - Error on table IO.FR_CAT5_CAPTION_REF, column ACTUAL_CALC_TX.
Field in data file exceeds maximum length


Data File
REPORT_SHORT_DESC_TX|REPORT_CAPTION_DESC_TX|Syntax - Actual|Syntax - Budget|Syntax - Variance to Budget
Value Map|Total Capital|BS."Total Assets".PTD_ACTUAL - BS."Total Current Liabilities".PTD_ACTUAL + BS."Bank Loans".PTD_ACTUAL + BS."Current Portion of Long-term debt".PTD_ACTUAL - BS."Other Long Term Liabilities".PTD_ACTUAL - BS."Accrued Employee Future Benefits Liability".PTD_ACTUAL|BS."Total Assets".PTD_BUDGET - BS."Total Current Liabilities".PTD_BUDGET + BS."Bank Loans".PTD_BUDGET + BS."Current Portion of Long-term debt".PTD_BUDGET - BS."Other Long Term Liabilities".PTD_BUDGET - BS."Accrued Employee Future Benefits Liability".PTD_BUDGET|Value Map."Total Capital".PTD_ACTUAL - Value Map."Total Capital".PTD_BUDGET



Thanks,
Sam Razz
Re: SQLLDR Error : Field in data file exceeds maximum length [message #278903 is a reply to message #278901] Tue, 06 November 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

SQL*Loader-00621: Field in data file exceeds maximum length
Cause: A field exceeded its maximum allowable length. The maximum length is either the length specified in the SQL*Loader control file, or, for delimitable fields without a length specified, the default maximum length (255 bytes).
Action: Check for missing delimiters and/or shorten the field.

You have to specify the length in your control file if it exceeds 255 bytes.

Regards
Michel
Re: SQLLDR Error : Field in data file exceeds maximum length [message #278910 is a reply to message #278901] Tue, 06 November 2007 04:47 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Thanks Micheal.

Can you please tell me how to increase the maximum length specified in SQL Loader?

Thanks,
Sam.
Re: SQLLDR Error : Field in data file exceeds maximum length [message #278913 is a reply to message #278910] Tue, 06 November 2007 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just specify CHAR(4000) in your field description in control file (just before TRIM...)

Regards
Michel
Re: SQLLDR Error : Field in data file exceeds maximum length [message #278915 is a reply to message #278913] Tue, 06 November 2007 05:02 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Thanks Micheal.

That workd Perfect!!!

Thanks,
Sam Razz
Re: SQLLDR Error : Field in data file exceeds maximum length [message #670509 is a reply to message #278913] Mon, 09 July 2018 22:13 Go to previous message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
I was struggling with the error and came across this on here and michel definitely saved me.
Previous Topic: Multibyte shift columns
Next Topic: SQL Loader Bind Size
Goto Forum:
  


Current Time: Thu Mar 28 13:32:14 CDT 2024