• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4322
  • Last Modified:

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.
0
2much4u
Asked:
2much4u
  • 3
  • 2
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
2much4uAuthor Commented:
I'm sorry but that doesn't solve my problem. Thanks.
0
 
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
 
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now