Oracle ODP.NET "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" [modified]

[C# Programming]

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 size - Size = Size *2. Actually, I solved the problem by setting - oParam.Size = oCmd.CommandText.Length - what the hell...? 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 size - Size = 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

dev

modified on Monday, June 29, 2009 2:47 AM