Stored Procedures and .NET winforms

[mvorkapich]

We have been using dotConnect for Oracle for some time now. However, we are now being asked to execute a stored procedure with an out parameter, which we have never done before. Clearly I have no idea what I'm doing and the Devart information on this has been hard to find. I have no access to the contents of the stored procedure but I know that it works because, when I run it in SQL Developer, I get the expected result.

The stored procedure is:
APPS.SBE_OPS_CELL_SN_WIP.GET_WIP_TRX_ID(ticketNumber);

When I execute this in SQL Developer:

DECLARE
transactionNumber NUMBER;
BEGIN
APPS.SBE_OPS_CELL_SN_WIP.GET_WIP_TRX_ID(transactionNumber);
dbms_output.put_line(transactionNumber);
END;

I get a result of 5004622 which is actually a sequential transaction number.

However, I have no idea how to accomplish this in .NET using dotConnect

Here's my latest attempt:
string proc = "APPS.SBE_OPS_CELL_SN_WIP.GET_WIP_TRX_ID(:txnID)";
OracleConnection oConn = db.oracleConn;
OracleCommand oCmd = new OracleCommand();
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.CommandText = proc;
oCmd.ParameterCheck = true;
OracleParameter param = new OracleParameter("txnID", OracleDbType.Number, ParameterDirection.Output);
oCmd.Parameters.Add(param);
oCmd.ExecuteNonQuery();

Console.WriteLine(param.Value);

However, when I get to ExecuteNonQuery, it jumps to my catch and gives me "Input string was not in a correct format".

If I change it to "APPS.SBE_OPS_CELL_SN_WIP.GET_WIP_TRX_ID" as suggested by the documentation, the code executes but my out parameter is null, which is not surprising since the Devart example is filling a table with a return value and this stored procedure doesn't return anything.

I have been able to successfully run stored procedures with no parameters and with in parameters in the past. However, the out parameter eludes me.

I have included the Devart example here for reference.

// Open the connection
OracleConnection connection
= new OracleConnection("Server=Ora; User Id=Scott; Password = tiger;");
connection.Open();

// Create a command
OracleCommand command = new OracleCommand();
command.Connection = connection;

// Set the CommandType property to execute
// stored procedures or functions by this command
command.CommandType = System.Data.CommandType.StoredProcedure;

// Set the name of procedure or function to be executed
command.CommandText = "get_all_depts_proc";

// The ParameterCheck property should be true to automatically
// check the parameters needed for the procedure execution.
command.ParameterCheck = true;

// At this moment, the command is ready for execution.
// As we have an output cursor parameter, we may use the command to fill a data table.
OracleDataTable dt = new OracleDataTable(command, connection);
dt.Fill();

And yes, my connection is open and working. I can query tables using SQL and that's working fine. db.oracleConn is defined and opened somewhere else. I'm sure it's something I'm just not getting. Any help would be appreciated.

[mvorkapich]

Well, you can ignore this. I resolved it myself. It turns out that this line:

oCmd.ParameterCheck = true;

Was the culprit. When I removed that, everything works. The only reason it was in there is because it's in the help file example.

[Shalex]

Thank you for letting us know that the issue is solved.

JIC: https://www.devart.com/dotconnect/oracl ... Check.html