Home » RDBMS Server » Server Utilities » error : maximum length excedeed (9i)
error : maximum length excedeed [message #342106] Thu, 21 August 2008 06:18 Go to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

Hi,
I am trying to load the flat file through the sqlloader.
In the control file there is a column name ISSUE_TEXT whose length is 4000.In the flat file the length of the ISSUE_TEXT is is excedding above 4000 so im facing the error.

I Cann't change the datatype of ISSUE_TEXT column to CLOb or BLOB .

what i need is to insert the records till 4000 length and reject the records which are beyond 4000 . I tried to use SUBSTR function for thsi but this is also throwing same error.

Please fidn the control file below.

LOAD DATA
CHARACTERSET UTF8
INFILE '%FILENAME%'
REPLACE
INTO TABLE IMP_SERVICEREPAIRTIPSDATA
FIELDS TERMINATED BY '^'
(
ISSUE_NBR CHAR,
PART_NBR CHAR,
LANG CHAR,
PART_DESC CHAR,
TSB_NBR CHAR,
ISSUE_TEXT CHAR "SUBSTR(:ISSUE_TEXT, 1, 3998)",
REV_DATE CHAR,
ROW_NUM SEQUENCE(MAX,1),
FEEDFile_ID CONSTANT '$$FF'
)

please suggest your inputs..........
Re: error : maximum length excedeed [message #342155 is a reply to message #342106] Thu, 21 August 2008 10:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
When you do not supply a length for the data in your text file, it defaults to an expected 255 characters per field and rejects anything larger, regardless of substr functions. You need to supply the maximum acceptable length, such as:

ISSUE_TEXT CHAR(4000),
Re: error : maximum length excedeed [message #342327 is a reply to message #342155] Fri, 22 August 2008 01:09 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

I had changed the control fie then also i am facing the same problem

Even we had used substr why the control file is not extracting only 4000 characters from the text file whose length is exceeding above 4000

IS the reason for Error is that we cann,t use bind variable exceeding above 4000 in function like SUBSTR,LENGTH etc???????

Record 685: Rejected - Error on table IMP_SERVICEREPAIRTIPSDATA, column ISSUE_TEXT.
Field in data file exceeds maximum length
Record 687: Rejected - Error on table IMP_SERVICEREPAIRTIPSDATA, column ISSUE_TEXT.
Field in data file exceeds maximum length

LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'C:\srtip.raw'
REPLACE
INTO TABLE IMP_SERVICEREPAIRTIPSDATA
FIELDS TERMINATED BY '^'
(
ISSUE_NBR CHAR,
PART_NBR CHAR,
LANG CHAR,
PART_DESC CHAR,
TSB_NBR CHAR,
ISSUE_TEXT CHAR(4000) TERMINATED BY "^" "SUBSTR(:ISSUE_TEXT, 1, 3998)",
REV_DATE CHAR,
ROW_NUM SEQUENCE(MAX,1),
FEEDFile_ID CONSTANT '1'
)

please suggest it's urgent

[Updated on: Fri, 22 August 2008 01:23]

Report message to a moderator

Re: error : maximum length excedeed [message #342338 is a reply to message #342327] Fri, 22 August 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The only thing urgent is to read OraFAQ Forum Guide.

Regards
Michel
Re: error : maximum length excedeed [message #342352 is a reply to message #342327] Fri, 22 August 2008 01:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
It works in 11g. In 9i, you might have to use dbms_lob.substr instead of substr. The parameters are reversed, so it would be:

ISSUE_TEXT CHAR(4000) "DBMS_LOB.SUBSTR(:ISSUE_TEXT, 3998, 1)"

but you shouldn't have to use the substr at all. Did you try it with just:

ISSUE_TEXT CHAR(4000)

with nothing else after it?

[Updated on: Fri, 22 August 2008 01:58]

Report message to a moderator

Re: error : maximum length excedeed [message #342372 is a reply to message #342352] Fri, 22 August 2008 03:14 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

Thanks for your reply & suggestions.

I am facing the same problem even after using dbms_lob in the ctl file.
Re: error : maximum length excedeed [message #342495 is a reply to message #342372] Fri, 22 August 2008 09:28 Go to previous message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
Since it works for me in 11g and I don't have 9i to test with anymore, all I can do is try to remember and guess at what might work. You might try things like making the char(4000) as large as the biggest expected string, like char(5000). You might also try creating a user-defined function that extracts the substring and using that in the control file, instead of just substr or dbms_lob.substr; I seem to recall that used to make a difference. If you use just char(4000) by itself without any substr or anything else it should at least allow all rows <= 4000 and reject those > 4000. It might help if you would post a complete test case as previously requested, including not just your control file, but create table statement and data file with a few rows of actual data.
Previous Topic: Importing Data from Flat file to Oracle Database Tables
Next Topic: Data file not loaded into proper sequence.
Goto Forum:
  


Current Time: Tue Dec 24 10:15:01 CST 2024