6502 from ODP.NET on a function returning string

Question

I have a SQL function defined as such:

create or replace function func_cmap_unit_test 
(what varchar2)
return varchar2 as 
begin
   return 'hello ' || what || '!';
end func_cmap_unit_test;

I tested in SQL Developer and it works fine:

select portal_ops.func_cmap_unit_test('world') from dual;

I have the following C# code:

Command.CommandText = funcName;
Command.CommandType = CommandType.StoredProcedure;

Command.Parameters.Add(
    new OracleParameter
    (
        "Output"
        , TypeMapping[typeof(T)]
        //, OracleDbType.Clob
        , ParameterDirection.ReturnValue
    )
);

foreach ((string name, object value) param in spParams)
{
    Command.Parameters.Add(param.name, param.value);
}
    if (Command.Connection.State == ConnectionState.Closed)
{
    Command.Connection.Open();
}
Command.ExecuteNonQuery();
return (T)Command.Parameters[0].Value;

The problem is that Command.ExecuteNonQuery keeps failing with

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

As you see in the commented code, when I switched the OracleDbType to Clob, the ExecuteNonQuery passed. I am perplexed why it wouldn't work for OracleDBType. Any help is greatly appreciated.

Answer 1

Technically, below code shows what you need. For output type of parameters of string types you need to provide size, otherwise it will be 1. Remember, that Parameter return value will be OracleString. You can check IsNull property on it

string retval = null;
using (var conn = new OracleConnection("conn str . . .")
{
    using (var cmd= new OracleCommand(funcName, conn)
    {
        cmd = CommandType.StoredProcedure;
        var p = new OracleParameter("out", OracleDbType.Varchar2, 200).Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(p);
        foreach ((string name, object value) param in spParams)
        {
            Command.Parameters.Add(param.name, param.value);
        }

        conn.Open();
        cmd.ExecuteNonQuery();
        retval = cmd.Parameters[0].Value.ToString();
    }
}