ODP.NET

Hi all and thanks for reading.
I am having a b**ch of a time getting the ODP to RETURN a varchar2 from a FUNCTION

Pretty straighforward, a very simple function in the package SCOTT.MYPACK :

function test_string_function ( )
return varchar2 IS
temp_string varchar2(100);
begin
temp_string := '1234567890';
return temp_string;
end;

Ok, nothing going in, a varchar2 coming out. This works fine in an anonymous block in sql+. (after I create a temp varchar2 of at least 100 to hold the results)

Here is how I am calling it:

string strConn = "Data Source=TESTSERV;User ID=scott;Password=tiger";
string strCmd = "MYPACK.test_string_function";
OracleConnection oConn = new OracleConnection(strConn);
OracleCommand oCmd = new OracleCommand(strCmd , oConn);
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add("return_string", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100, System.Data.ParameterDirection.ReturnValue);
oCmd.Connection.Open();
try
{
int k = oCmd.ExecuteNonQuery();
}
catch (OracleException oe)
{
Console.WriteLine(oe.Message);
}

This throws an exception with this message:
ORA-06502: PL/SQL: numeric or value error: character
string buffer too small ORA-06512

It seems that the data provider wants me to allocate memory before hand for the returning string.

Note, if the function is returning a fixed length data type (like an int or a date or ...) the ODP has no problem. (of course I change my parameter type to match)

I have had problems before with stored proc varchar2 OUTPUT params where you have to make sure the parameter is padded out with plenty of space before making the call, but what is the deal here ???
Thanks
JH