ORA-06502: PL/SQL: numeric or value error: character string buffer too small whenever I have an OUT Parameter

Question

I know there's already a few posts on this error ( "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" is bug?), but I've been through them and I still can't seem to figure out what's wrong with my code.

I can't seem to call stored procedures with an OUT parameter in C#.

I have this stored procedure.

CREATE OR REPLACE PROCEDURE test_sp(v_payload OUT VARCHAR2) AS
BEGIN
    v_payload := 'he2';
END;

It really is as simple as I can make it. It works in Oracle SQL Developer.

But in my c# code.

        OracleCommand command = new OracleCommand("test_sp", con);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new OracleParameter("v_payload", OracleDbType.Varchar2, 32767, ParameterDirection.Output));
        await command.ExecuteNonQueryAsync();

It'll return this error

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

Answer 1

You're using the incorrect constructor. The constructor you're using is:

public OracleParameter(string name, System.Data.OracleClient.OracleType oracleType, 
                       int size, string srcColumn);

And it appears string srcColumn is implicitly converting ParameterDirection.Output to string.

Try this instead:

var param = new OracleParameter("v_payload", OracleDbType.Varchar2, 32767);
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);