C# Oracle execute stored procedure with output parameter

Question

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

Have you tried calling the procedure directly, i.e. with a PL/SQL script? So just to exclude the possibility that the procedure itself doesn't return. — Martin Klinke
– Martin Klinke, Commented Jun 27, 2011 at 9:58
If I call the stored procedure in SqlNavigator it works perfectly. And I can't set the Direction directly in Add can I? I could add .Direction = ... but then it won't return the OleDbParameter. — Rik De Peuter
– Rik De Peuter, Commented Jun 27, 2011 at 10:01
Have you tried with a different type for the parameters instead of OleDbType.Double? The identifier should rather be an Integer (or BigInt) I guess. — Martin Klinke
– Martin Klinke, Commented Jun 27, 2011 at 10:17
I used Double because when I used an XSD scheme to get data it converted it to Doubles. But even when I try with Decimal/Integer/BigInt it didn't work as well. — Rik De Peuter
– Rik De Peuter, Commented Jun 27, 2011 at 10:46

Rik De Peuter · Accepted Answer · 2011-06-27 12:09:23Z

2

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

answered Jun 27, 2011 at 12:09

Rik De Peuter

7132 gold badges10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Oracle execute stored procedure with output parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related