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.
