OracleData provider for dot net gives ORA-01406:fetched column when fetching large string stored in a long column in the Oracle 9i DB

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: ANCTeam Tribal via .NET 247 (anonymous_at_dotnet247.com)
Date: 03/07/05 

Date: Mon, 07 Mar 2005 11:14:21 -0800

(Type your message here)

--------------------------------
From: ANCTeam Tribal

Hi All,
We are using a column of long type Oracle Databse.This contains some string which sometimes exceeds to 60000 characters or more than that. We have used OracleDataProvider for dot net. When we try fetch this
column value through a query from the application, it returns the entire value. But when we used a stored procedure having the same query, it generates the ORA-01406:fetched column value was truncated.
Is there any solution to achieve this. I am attaching the sample code here.
Love to all
Ashish,

 Here is the code from dot net client

 

System.Data.OracleClient.OracleDataReader dr;

System.Data.OracleClient.OracleParameter param1 = new System.Data.OracleClient.OracleParameter ("IN_intEntityTypeCode",System.Data.OracleClient.OracleType.VarChar );

System.Data.OracleClient.OracleParameter param2 = new System.Data.OracleClient.OracleParameter ("IN_intEntityUniqueID",System.Data.OracleClient.OracleType.Number);

 

System.Data.OracleClient.OracleParameter param3= new System.Data.OracleClient.OracleParameter ("OUT_curRecords",System.Data.OracleClient.OracleType.Cursor);

 

 

                  try

                  {

                        

 

                        param1.Direction = ParameterDirection.Input ;

                        param1.Value = "12";

                        oracleCommand1.Parameters.Add (param1) ;

 

                        

                        

                        param2.Direction = ParameterDirection.Input ;

                        param2.Value = 15052;

                        oracleCommand1.Parameters.Add (param2);

                                          

                        param3.Direction = ParameterDirection.Output ;

                        oracleCommand1.Parameters.Add (param3);

                        

 

                        oracleCommand1.Connection = oracleConnection1;

                        oracleCommand1.CommandType=CommandType.StoredProcedure;

                        oracleCommand1.CommandText = "CFG_INVENTORY_NON_VER_CTL_SEL";

 

  

                        oracleConnection1.Open ();

                        dr = oracleCommand1.ExecuteReader ();

 

 

                        dr.Read ();

                        MessageBox.Show(dr["ENTITYXML"].ToString ());

                  }

                  catch (Exception ex)

                  {

                        MessageBox.Show (ex.Message );

                  }

 

Stored Procedure is as follows:

 

CREATE OR REPLACE PROCEDURE

    "FES"."CFG_INVENTORY_NON_VER_CTL_SEL" (

IN_intEntityTypeCode IN cfg_entityinventory.entitytype_code%TYPE,

                        IN_intEntityUniqueID IN cfg_entityinventory.entityuniqueid%TYPE,

                        OUT_curRecords OUT cfg_types_pkg.IOcursor

)

AS

  OutCursor cfg_types_pkg.IOcursor;

  BEGIN

    OPEN OutCursor FOR

 

   SELECT * FROM CFG_ENTITYINVENTORY WHERE ENTITYUNIQUEID = IN_intEntityUniqueID

   AND ENTITYTYPE_CODE = IN_intEntityTypeCode;

                 

   OUT_curRecords := OutCursor;

     

END;

-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)

<Id>f/xkjD2ghEWFVWWf6ZlVFA==</Id>