ORA-01406 - Fetched column value was truncated

Hello,

I receive this error sometimes when I execute a procedure in a Package in Oracle 10g, the data returned has a CLOB field and some numbers and nvarchar2, could this be the problem?

Thanks.
2much4uAsked:
Who is Participating?
 
helpneedCommented:
hi

Cause: In a host language program, a FETCH operation was forced to truncate a character string. The program buffer area for this column was not large enough to contain the entire string. The cursor return code from the fetch was +3.
Action: Increase the column buffer area to hold the largest column value or perform other appropriate processing.



in plsql, you can only do this if the long raw is less than 32k -- anything over
that, plsql cannot fetch it.

in other languages, you have to fetch it all and see how much you get.

if you use blob, (you can to_lob a long raw), you can find this easily.

create table t as select to_lob(long_raw_column) data from t2 <where ...>
select dbms_lob.getlength(data) from t;

 also

try these type of inserts inthat procedure better to convert and insert

insert into test_blob_tab
select 1, to_lob(text_long_raw)
  from text_long_raw_table
where id = .......;


regards

0
 
Helena Markováprogrammer-analystCommented:
Maybe this can help (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/e900.htm#1004903):

ORA-01406 fetched column value was truncated

Cause: In a host language program, a FETCH operation was forced to truncate a character string. The program buffer area for this column was not large enough to contain the entire string. The cursor return code from the fetch was +3.

Action: Increase the column buffer area to hold the largest column value or perform other appropriate processing.
0
 
2much4uAuthor Commented:
I found that answer but I don't understand it, how can I Increase the column buffer area?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Helena Markováprogrammer-analystCommented:
It can be a bug, you can find some of them on MetaLink. You can try to drop column and create it.
Maybe someone else can help you more than I have done.
0
 
2much4uAuthor Commented:
I'm sorry but that doesn't solve my problem. Thanks.
0
 
2much4uAuthor Commented:
hi helpneed,

I think that your comment doesn't solve my problem.

This is my query:

----------------------------------------------------------------------------------------
SELECT   MC_MP.c_id, MC_MP.d_title AS d_title,
         MC_MP.f_dateon, MC_USERS.d_firstname, MC_USERS.d_lastname,
         MC_USERS.d_email, ROWNUM
    FROM MC_MP, MC_USERS
   WHERE (    (MC_USERS.c_id(+) = MC_MP.c_usr_id)
          AND (MC_MP.b_validated = 1)
          AND (MC_MP.c_mic_id = 3)
          AND (MC_MP.c_geo_id = 4597784)
         )
ORDER BY MC_MP.f_dateon DESC;
----------------------------------------------------------------------------------------

If I execute this query from TOAD everything is working fine, but if I execute it from my asp.net page as asimple query or inside a stored procedure it returns ORA-01406 error.

If I remove MC_MP.d_title NVARCHAR2(255) the query works fine from anywhere.

Regards!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.