OraFAQ Forum: Server Utilities » CLOB Field in data file exceeds maximum length

Home » RDBMS Server » Server Utilities » CLOB Field in data file exceeds maximum length

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CLOB Field in data file exceeds maximum length [message #180869]

Wed, 05 July 2006 10:35

SRISREENATH
Messages: 13
Registered: January 2006

Junior Member

Hi folks,
This is sreenath. I am using oracle 8.1.7 in my system.
I have a data file which I need to insert that data into a table which is having 4 CLOB columns. when I try to insert the data through SQL Loder I am getting badfiles. In the lob file it is specifying like "Field in data file exceeds maximum length".

Let me explain which I have faced the problem with the same.
DDL of my table is
CREATE TABLE GM_OPTION_RULES_INTERFACE
(
SYSTEM_KEY VARCHAR2(15),
MODL_YR_NBR VARCHAR2(5),
VEH_PROD_CD VARCHAR2(15),
SELLNG_SRC_CD VARCHAR2(15),
NAMPLT_CD VARCHAR2(15),
OPTN_CD VARCHAR2(6),
SET_ID VARCHAR2(4),
MDSNG_MODL_DESGTR VARCHAR2(500),
AVAIL_CODE VARCHAR2(3),
START_DATE VARCHAR2(10),
END_DATE VARCHAR2(10),
FIELD1 VARCHAR2(15),
OPERATOR1 VARCHAR2(2),
VALUE1 VARCHAR2(200),
FIELD2 VARCHAR2(15),
OPERATOR2 VARCHAR2(2),
VALUE2 VARCHAR2(200),
FIELD3 VARCHAR2(15),
OPERATOR3 VARCHAR2(2),
VALUE3 VARCHAR2(200),
FIELD4 VARCHAR2(15),
OPERATOR4 VARCHAR2(2),
VALUE4 VARCHAR2(200),
FIELD5 VARCHAR2(15),
OPERATOR5 VARCHAR2(2),
VALUE5 VARCHAR2(200),
FIELD6 VARCHAR2(15),
OPERATOR6 VARCHAR2(2),
VALUE6 VARCHAR2(200),
FIELD7 VARCHAR2(15),
OPERATOR7 VARCHAR2(2),
VALUE7 VARCHAR2(200),
FIELD8 VARCHAR2(15),
OPERATOR8 VARCHAR2(2),
VALUE8 VARCHAR2(200),
FIELD9 VARCHAR2(15),
OPERATOR9 VARCHAR2(2),
VALUE9 VARCHAR2(200),
OPTN_FAMILY_CD VARCHAR2(4),
OPTN_FAMILY_TYPE_CD VARCHAR2(1),
PROCESS_FLAG VARCHAR2(1),
CNTRY_CD VARCHAR2(2),
LANG_CD VARCHAR2(2),
ERROR_MESSAGE VARCHAR2(255),
MODIFIED_CD VARCHAR2(2),
DUMMY_NODE_OPTN_CD VARCHAR2(10),
DUMMY_MT_NODE_OPTN_CD VARCHAR2(1000),
USAGE CLOB,
OLD_USAGE CLOB,
DROPPED_USAGE CLOB,
AVAIL_USAGE CLOB
);
This is my table.

My control file is
load data
infile 'C:\OptnReleaseRules.dat'
append
into table gm_option_rules_interface
when (sellng_src_cd <> '14')
and (sellng_src_cd <> '23')
and (sellng_src_cd <> '72')
fields terminated by '^'
trailing nullcols
(
System_key sequence(1,1),
Modl_Yr_Nbr position(*+7),
Veh_Prod_cd ,
Sellng_Src_cd ,
Namplt_cd ,
Optn_cd ,
Set_Id ,
Mdsng_modl_desgtr ,
Usage nullif usage=BLANKS,
Avail_Code ,
Start_Date ,
End_Date ,
Field1 nullif field1=BLANKS,
Operator1 ,
Value1 ,
Field2 ,
Operator2 ,
Value2 ,
Field3 ,
Operator3 ,
Value3 ,
Field4 ,
Operator4 ,
Value4 ,
Field5 ,
Operator5 ,
Value5 ,
Field6 ,
Operator6 ,
Value6 ,
Field7 ,
Operator7 ,
Value7 ,
Field8 ,
Operator8 ,
Value8 ,
Field9 ,
Operator9 ,
Value9
--, cntry_cd ,
-- lang_cd ,
--error_message
)

dat file is like this
000011^2006^12^13^001^2AC^06^CS15403/CS15603/CS15643/CS15653/CT15403/CT15643/CT15653^&CAP&FLT-FVX/R6D/VN9/VX7.^A^^^OrdTyp^EQ^ FDR/FLS/FNR/FRC/FEF

Log file had created like this.

Record 315: Rejected - Error on table GM_OPTION_RULES_INTERFACE, column USAGE.
Field in data file exceeds maximum length

Record 10642: Rejected - Error on table GM_OPTION_RULES_INTERFACE, column MDSNG_MODL_DESGTR.
Field in data file exceeds maximum length

like this it had created lot of recored
MDSNG_MODL_DESGTR is not CLOB but this is also errored out.

can you any one please give me solution to resolve this uissue.

Thanks & Regards
Sreenath.

Report message to a moderator

Re: CLOB Field in data file exceeds maximum length [message #180894 is a reply to message #180869]

Wed, 05 July 2006 14:18

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Unless you specify a maximum data length for a column in your SQL*Loader control file, the default is 255. So, you need to use something like the following for each column that may have values greater than 255 characters:

column_name CHAR (4000)

Report message to a moderator

Previous Topic:	.CSV file to .CTL file
Next Topic:	using sqlldr i am not able to add data more than 255 character in Varchar2(4000) defined field

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 08:50:55 CST 2024