C# ODB.NET Out parameter is always DBNull

Question

I have a stored procedure within a package in Oracle SQL Server. This procedure has input and output parameters. The procedure itself works and when executed in Oracle SQL Developer the output parameter reads the expected output (column value of last row). This procedure has to be called by a Web API through ODP.NET (Oracle Managed Data Access).

The problem: The output parameter (of type Long in ODP and NUMBER(12,0) in db) is never returned. The output parameter is always DBNull after executing the command.

Below are the relevant parts of my code (other parameters are omitted): The Package Procedure:

PROCEDURE post_event (event_id OUT OPIS_REGISTRATIE_PROJ.PROJECT_ID%TYPE)
IS BEGIN
-- Some stuff that's happening (that works)
SELECT MAX(PROJECT_ID) INTO event_id FROM OPIS_REGISTRATIE_PROJ WHERE PERSOON_ID = worksheet_id;
END;

The parameter that is used:

parameters.Add(new OracleParameter("event_id", OracleDbType.Long, ParameterDirection.Output));

The calling code in C#:

if (con.State == ConnectionState.Open)
{
    // Create command and set parameters
    using (var cmd = new OracleCommand(cmdText, con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (var p in parameters)
        {
            cmd.Parameters.Add(p);
        }

        // Execute the stored procedure
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (OracleException ex)
        {
            Console.WriteLine(ex.Message);
        }

long paramEventId = 0;
if (cmd.Parameters["event_id"].Value != DBNull.Value)
{
    paramEventId = (long)(OracleDecimal)cmd.Parameters["event_id"].Value;
}

As you can see, I know how to check for a DBNull value, but that's not the issue. It is not supposed to get a DBNull value.

Some things I've tried so far:

• Change Parameter DBType to Int64 and others (with others I correctly get an error about the parameter type)
• Set the event_id hardcoded with a value (event_id := 1234567;)
• Execute the SELECT MAX(... query outside of the procedure directly in the database.

Everything I try though SQL Developer works as expected. It's the transfer of the Output parameter to the calling C# API that has problems. I've googled a lot and searched Stackoverflow also, so I hope someone can help me with an answer that works.

Answer 1

As mentioned in my question, I've tried to change the DbType of the Output-parameter to Int64. When reviewing, I noticed there was another (IN OUT) parameter that was declared as a DbType.Long. When changing one, I didn't change the other, so apparently that still gave an error.

The error I was getting was:

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

The solution: Change all OracleDbType.Long parameters to be OracleDbType.Int64.

Such an easy answer in hindsight. Still I don't understand why this is an issue.
Isn't Long supposed to be the same/an alias for Int64?

I'm sorry to have bothered you and I appreciate the time you took to try and help. :)
I hope someone can fix their (similar) problems with my question/answer.