none
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    Question

  • Hello

    I am running into "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" and I managed to get around it by setting oParam.Size to length of command text instead of the parameter itself!?!

    The stored proc signature is

    CREATE OR REPLACE PROCEDURE spXXXXX (
    TestId varchar2,
    bAllTestCleared OUT varchar2
    )
    ...

    And my code/C# - yes first place I checked is that I have set parameter size of course:

    void SomeTest()
    {
    IDbConnection oConn = null;
    IDbCommand oCmd = null;
    IDataParameter oParam = null;
    IDataParameter oOutParam = null;

    string strTestId = null;

    object oReturnValRaw = null;

    try
    {


    strTestId = Guid.NewGuid().ToString();

    ...
    oConn = DBUtil.GetDefaultDBConnection();
    oConn.Open();

    oCmd = oConn.CreateCommand();
    oCmd.CommandText = "spXXXXX";
    oCmd.CommandType = System.Data.CommandType.StoredProcedure;

    oParam = oCmd.CreateParameter();
    oParam.Value = strTestId;
    oParam.ParameterName = ":TestId";
    oParam.DbType = DbType.String;
    ((OracleParameter) oParam).Size = strTestId.Length; // Also tried doubling length: Size = strTestId.Length*2. Actually, I solved the problem by setting - oParam.Size = oCmd.CommandText.Length - what the ____...? It worked, but I have no idea why!?
    oParam.Direction = ParameterDirection.Input;
    oCmd.Parameters.Add(oParam);

    oOutParam = oCmd.CreateParameter();
    oOutParam.ParameterName = ":bAllTestCleared";
    oOutParam.DbType = DbType.String; // one character string, either: 'Y' or 'N'
    ((OracleParameter) oOutParam).Size = 1; 
    // Also tried doubling length: Size = 2
    oOutParam.Direction = ParameterDirection.Output;
    oCmd.Parameters.Add(oOutParam);

    oCmd.ExecuteNonQuery();

    oReturnValRaw = oOutParam.Value;
    ...
    }
    catch (Exception ex)
    {
    ...
    } finally {...}
    }


    Any suggestion? Is this a bug?
    http://forums.oracle.com/forums/thread.jspa?messageID=3258848

    Thanks



    My environment:
    1. I'm using ODP.NET 11.1.0.7.10 BETA downloaded from
    http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html

    The package includes Oracle Data Provider for .NET 2.0 11.1.0.7.10 Beta - from my app.config I have made sure I'm using the correct version by

    <qualifyAssembly partialName="Oracle.DataAccess" fullName="Oracle.DataAccess, Version=2.111.7.10, Culture=neutral, PublicKeyToken=89b483f429c47342"/>

    2. Database: 11g Express
    • Edited by devvvy Monday, June 29, 2009 6:48 AM
    Monday, June 29, 2009 4:15 AM

Answers