| error : maximum length excedeed [message #342106] |
Thu, 21 August 2008 06:18  |
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 #342327 is a reply to message #342155] |
Fri, 22 August 2008 01:09  |
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 #342352 is a reply to message #342327] |
Fri, 22 August 2008 01:57 |
 |
Barbara Boehmer
Messages: 9092
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 #342495 is a reply to message #342372] |
Fri, 22 August 2008 09:28 |
|
Barbara Boehmer
Messages: 9092
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
