Oracle ODP.NET "ORA-06502: character string buffer too small"

[THY02K]

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; 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 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/thre...sageID=3258848

Thanks

My environment:
1. I'm using ODP.NET 11.1.0.7.10 BETA downloaded from
http://www.oracle.com/technology/sof...10710beta.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

[Arjay]

Are you setting the wrong parameter?

((OracleParameter) oParam).Size = 1; // Also tried doubling length: Size = 2;

Shouldn't this be...

oOutParam.Size = 1;

[THY02K]

Sorry no, it was me making a mistake when I copy/paste/edit code into this discussion thread - it correct in original code.

I've corrected original post, thank you for your reminder.

[Arjay]

I noticed you have cast the param to an Oracle Parameter.

Rather than using the oCmd.CreateParameter( ) method, just create an OracleParameter directly.

OracleParameter oOutParam= new OracleParameter(":bAllTestCleared", OracleDbType.Varchar2);
oOutParam.Direction = ParameterDirection.Output;
oOutParam.Size = 50;
oCmd.Parameters.Add(oOutParam);