ORA-03146 when getting output parameter from Oracle SQL procedure with System.Data.Common.DbCommand

I'm trying to call an Oracle SQL procedure that has output parameters from ASP.NET using DbCommand, as this does not seem to be possible with NHibernate abstractions.

I was not able to find relevant documentation for the Oracle + ASP.NET combo, so I tried to play around with DbCommand until reaching something that should almost work, except it does not. When running the code below, I get an error

ORA-03146: invalid buffer length for TTC field

I've seen some similar questions, but they use syntax that does not seem to be supported in my version e.g.

command.Parameters.Add(":P1", DbType.Decimal).Direction = ParameterDirection.Output;

My code:

using var session = NHTransactionScope.CreateNewSession(GetConnection());
using var command = session.Connection.CreateCommand();

var pOut = command.CreateParameter();
pOut.Direction = ParameterDirection.Output;
pOut.ParameterName = "v_result";
pOut.DbType = DbType.Int64;
command.Parameters.Add(pOut);
var pIn = command.CreateParameter();
pIn.ParameterName = "p_messageId";
pIn.Value = 123;
command.Parameters.Add(pIn);

command.CommandText = "CALL GET_PROCESS_TO_RUN_FROM_MESSAGE(:v_result, :p_messageId)";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
session.Close();

Console.Write($"Output is {pOut.Value}");

I don't have control over the procedure, and I can't change it. Here's a simplified version of it. I can call the procedure from SQL just fine.

CREATE OR REPLACE PROCEDURE GET_PROCESS_FROM_UG_MESSAGE(p_processToRun OUT NUMBER, p_messageHistoryId IN MESSAGE_HISTORY.ID%TYPE)
AS
BEGIN
    -- some logic should be in there, but it's not relevant
    p_processToRun := 1;
END;

-- calling the procedure
DECLARE
    v_result NUMBER;
    v_history_id NUMBER := 100; 
BEGIN
    GET_PROCESS_FROM_UG_MESSAGE(v_result, v_history_id);
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result); -- Outputs 'Result: 1'
END;

So what's the correct way of calling a PL-SQL procedure that has output parameters?

Solution

I would go with

command.CommandText = "GET_PROCESS_TO_RUN_FROM_MESSAGE"; 
command.CommandType = CommandType.StoredProcedure;

But your issue most likely is here - In SP you have p_processToRun OUT NUMBER. Your param however, is set to DbType.Int64;. number is same as number(38) - way larger than datatype you allocated. Make your stored procedure compatible and declare type as number(18). Almost never that you need to declare Oracle numeric type as number without precision.

And we don't see what type is here MESSAGE_HISTORY.ID%TYPE