Devart Forums

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.

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.

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

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