CODEWITHSUNDEEP
c#oracle-databaseparametersprocedure
Rik De Peuter
Rik De Peuter

Reputation: 713

C# Oracle execute stored procedure with output parameter

Situation:
I'm trying to run a stored procedure that has an output parameter, which I need to catch.
I use C# 3.5 and the OracleClient with an OleDbConnection.

Research:
I've been looking around for other ways, but as far as I can tell I'm doing it correct. Microsoft support and various other forums.

Problem:
When I do the cmd.ExecuteNonQuery() it just gets stuck. No error or anything, it just stops there and holds the Thread.
When I try via OleDbDataReader or the Scalar it's nothing better.
If I change the CommandText (remove package name) it gives error that it can't find the stored procedure, so I know that is correct at least.

Code:
Oracle:

PROCEDURE deleteThemakaart
(an_seqthemakaart IN NUMBER, an_retval OUT NUMBER)
....

C#:

double InputValue = 777;
try
{
    OleDbConnection con = new OleDbConnection(...);
    con.Open();

    OleDbCommand cmd = new OleDbCommand()
    {
        CommandText = "thema.pckg_themakaarten.deleteThemakaart",
        Connection = con,
        CommandType = CommandType.StoredProcedure,
    };

    OleDbParameter input = cmd.Parameters.Add("an_seqthemakaart", OleDbType.Double);
    OleDbParameter output = cmd.Parameters.Add("an_retval", OleDbType.Double);

    input.Direction = ParameterDirection.Input;
    output.Direction = ParameterDirection.Output;

    input.Value = InputValue;

    cmd.ExecuteNonQuery();

    return (double)output.Value;
}
catch (Exception ex)
{
    ...
}
finally
{
    con.Close();
}

Any help is very welcome :)

Edit: some code is below in comment, but hasn't gotten me any further so far :(

Greetings

Upvotes: 2

Views: 21465

Answers (1)

Rik De Peuter
Rik De Peuter

Reputation: 713

I found the trouble maker... one of the tables that the procedure used was locked.

Upvotes: 2

Related Questions