Sorry, guys. Late to the thread. I had seen this error too a long time ago and had the same fun task of isolating the problem records. In our situation the source of the records was a feed from another system. I never did figure out what was actually wrong with the data. I ended up adding a filter on submit that does a LEFT(<field>,<field size>) on the char fields that aren't unlimited size and are set directly by that external data. I have not seen the error since.
Good luck! Dennis "Martin, Robert" <[email protected]> Sent by: "Action Request System discussion list(ARSList)" <[email protected]> 02/19/2010 09:26 AM Please respond to [email protected] To [email protected] cc Subject Re: ORA-01406: fetched column value was truncated SOLVED ** Joe, Thanks for the good ideas. This problem was in our test system, so I had no worries about blowing away the data. But I?ll save your email just in case the same thing happens in our live system. More Cheers, Dwayne From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Joe D'Souza Sent: Thursday, February 18, 2010 5:30 PM To: [email protected] Subject: Re: ORA-01406: fetched column value was truncated SOLVED ** Dwayne, You could try asking the person who last modified that entry what he or she did to input into C8. Like John I feel it must have had multi-byte characters too that shot it over the allowable limit. That would also explain why length() didn't catch it, perhaps lengthb() would have as it checks the length of a field in bytes.. There is also a function lengthc() that uses complete Unicode characters.. One of these might have been better than just length() If you still have the original data, you might be able to play around with these and find out what you get and then reset that column length accordingly and preserve the original data.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]]on Behalf Of Martin, Robert Sent: Thursday, February 18, 2010 12:06 PM To: [email protected] Subject: Re: ORA-01406: fetched column value was truncated SOLVED ** I don?t know how multi-byte chars work. I think that the data would probably have been typed in from a keyboard, but it is possible that somebody copied and pasted something strange. Dwayne From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of John Sundberg Sent: Thursday, February 18, 2010 11:52 AM To: [email protected] Subject: Re: ORA-01406: fetched column value was truncated SOLVED ** Impressive find -- that would have been tough. Is it possible that something to do with multi-byte chars caused a problem? -John On Feb 18, 2010, at 8:51 AM, Martin, Robert wrote: ** I tried Joe?s suggestion and did ?select max(length(cn)) from T570;? for each of the character fields, but none seemed to be over the maximum length. So then I ran: ?select c1 from T570 order by c1 desc;? This gave me a list of all the entry IDs for the form. I opened the form in Remedy and ran a search for all entry IDs > the last ID on the list, and it crashed. Then I tried the top of the list and it didn?t crash, so I tried the middle etc till I found the offending entry. The entry?s C8 field looked suspiciously long so I used direct sql to set to a shorter value. And that fixed the problem. I don?t know why it didn?t show up in the ?select max(length(cn))? query. Maybe there was something wrong with the data besides the length. Thanks for your help, Joe, Dwayne From: Action Request System discussion list(ARSList) [ mailto:[email protected]] On Behalf Of Joe D'Souza Sent: Wednesday, February 17, 2010 4:59 PM To: [email protected] Subject: Re: ORA-01406: fetched column value was truncated ** Robert, Appears like you might have some corruption in your ARS meta data.. Go into SQL Developer. Describe T570, and check the lengths of each of those selected columns reports. Then open up your Developer Studio or Admin Client whatever you use, and check the lengths there.. Check for any discrepancies.. correct them if any. Also before correcting that, you may want to run a select max(Cn) from T570 for each of those columns to check what is the max length of data in each of those columns to give you an idea of what your column sizes should be.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [ mailto:[email protected]]on Behalf Of Martin, Robert Sent: Wednesday, February 17, 2010 4:15 PM To: [email protected] Subject: ORA-01406: fetched column value was truncated ** Dear List, We have a form that we have been using for years, but when I try to do a unqualified search I get ?: ORA-01406: fetched column value was truncated.? The sql log shows the same message, but no indication of what is causing it. The sql log selection statement is: SELECT * FROM ( SELECT T570.C1,C2,C561000257,C561000214,C8 FROM T570 ORDER BY 1 ASC ) WHERE ROWNUM <= 2147483648. I can paste this statement into SQL Developer, and it runs with no problem. I can open individual entries with no problem, but somewhere there must be a problem entry. How can I track it down? (ARS 7.1 p3, RH Linux server, Oracle 10.2 db) Dwayne Martin James Madison University _Platinum Sponsor: [email protected] ARSlist: "Where the Answers Are"_ _Platinum Sponsor: [email protected] ARSlist: "Where the Answers Are"_ -- John Sundberg Kinetic Data, Inc. "Building a Better Service Experience" Recipient of the WWRUG09 Innovator of the Year Award [email protected] 651.556.0930 I www.kineticdata.com _Platinum Sponsor: [email protected] ARSlist: "Where the Answers Are"_ _Platinum Sponsor: [email protected] ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"
