ORA-06502 error: Char type value fails to insert/update

[sachin]

Hi All,

The issue is whenever we make any calls to the methods in ODS from .NET, if the method holds a CHAR type of parameter, then it fails all the time. Please help us to resolve it ASAP.

The insert_row procedure (as the sql below) is using p_col1 as a CHAR type parameter. So whenever, we make a call from .net to this method, we have noticed that 5 characters value sent from .net is getting converted to 45 characters value with additional spaces appended to the end of the string.
In .Net code, we have mentioned the type of the parameter (OracleDbType) as ‘Devart.Data.Oracle.OracleDbType.Char’ and size(Size) as 5.
We tried with changing the type in .Net code to ‘Devart.Data.Oracle.OracleDbType.Varchar’, still the same issue occurs. But if we change the PL/SQL procedure in such a way that, the type of p_col1 is changed to VARCHAR2, then the method is successful.

We have used the errbuf OUT parameter for checking the length and the value of the parameter received in PL/SQL.

The connection string in use to connect oracle from .net code is -

Code: Select all

connectionString="User Id=****;Password=*****;Server=****;Direct=True;Sid=****;Persist Security Info=True;Port=1521;Connection Timeout=180;Pooling=True;Max Pool Size=100;" providerName="Devart.Data.Oracle"

In order to reproduce the error let there be a table as -

Code: Select all

CREATE TABLE TEST_TABLE (Col1 CHAR(5))
/

The procedure getting call is as follow -

Code: Select all

CREATE OR REPLACE PROCEDURE insert_row(p_col1 IN OUT CHAR
                                      ,errbuf    OUT VARCHAR2
                                      ,retcode   OUT VARCHAR2)
AS
rec TEST_TABLE%ROWTYPE;
BEGIN
  errbuf := NULL;
  retcode:= '0';

  errbuf := errbuf || 'The value of the parameter p_col1 = '||p_col1||'. And the length is = '||LENGTH(p_col1); 

  rec.col1 := p_col1;

  INSERT
    INTO test_table
        (col1)
  SELECT rec.col1
    FROM dual;

EXCEPTION
  WHEN OTHERS THEN
    errbuf  :=errbuf || '. Unexpected error occured in the procedure pk.insert_row. '||SUBSTR(SQLERRM,1,255);
    retcode := TO_CHAR(SQLCODE);
END;

And the C# code where we are calling the Oracle procedure -

Code: Select all

public void InsertPickListData()

{
    using (OracleConnection con = new OracleConnection(ConnectionString))
{
     OracleCommand objCommand = new OracleCommand();
     objCommand.Connection = con;
     objCommand.CommandText = "pk.insert_row";
     objCommand.CommandType = CommandType.StoredProcedure;
     
     OracleParameter parameter = new OracleParameter();
      parameter.Direction = ParameterDirection.InputOutput;
      parameter.OracleDbType = OracleDbType.Char;
      parameter.ParameterName = "p_col1";
      parameter.Size = 5;
      parameter.Value = "abc12";

      objCommand.Parameters.Add(parameter);
      
      parameter = new OracleParameter();
      parameter.OracleDbType = OracleDbType.VarChar;
      parameter.ParameterName = "errbuf";
      parameter.Direction = ParameterDirection.Output;

       objCommand.Parameters.Add(parameter);

      parameter = new OracleParameter();
      parameter.OracleDbType = OracleDbType.VarChar;
      parameter.ParameterName = "retcode";
      parameter.Direction = ParameterDirection.Output;
      
      objCommand.Parameters.Add(parameter);
      
      try
     {
             con.Open();
             int updateSuccees = objCommand.ExecuteNonQuery();
             con.Close();
      }
      catch (Exception ex)
     {
            Logger.LogInformation("Exception has occurred in   PickListDataAccess.InsertPickListData");
            bool rethrow = ExceptionHandler.HandleException(ex, "SqlException");
            if (rethrow)
           {
               Logger.LogException(ex);
           }
     }
}

The errbuf value is recieved as follow:

Code: Select all

The value of p_col1 is = abc12                                        . And its length is = 45. Unexpected error encountered in PK.insert_row: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The version of Oracle is -

Oracle Database 10g Ebterprise Edition Release 10.2.0.5.0 - 64bit
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

and the DevArt DotConnect version is -
Devart DotConnect for Oracle - 6.10.126.0
DevArtEntitiy Developer - 3.20.104.0

[Shalex]

Please upgrade to the latest (6.80.341) version of dotConnect for Oracle. The problem is already fixed.