ODP.NET VB.Net calling a stored procedure and returning a refCursor

Question

This problem has driven me mad for over a day now. I can create a connection to the database, I can execute sql and return results from that but I can't seem to call a stored Procedure. Here is the code

    Dim myCMD As New OracleCommand
    Dim TheDataReader as New OracleDataReader

    myConnection1.Open()

    myCMD.Connection = myConnection1
    myCMD.CommandType = CommandType.StoredProcedure
    myCMD.CommandText = "WS_DATA_LAYER.select_user_groups"

    myCMD.Parameters.Add(New OracleParameter("id_user", OracleDbType.VarChar2)).Value = "TXA"
    myCMD.Parameters.Add(New OracleParameter("ws_rs", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

    ' Tried every single execute function here and none have worked
    ' Either error is thrown or empty refcursor
    myCMD.ExecuteScalar()

    TheDataReader = myCMD.Parameters(1).Value().GetDataReader()

The Problem lies in ExecuteScalar at the moment. It's throwing an exception called "Input string was not in a correct format". I've tried passing the string with Oracle single quotes and get the same thing. If I use

    TheDataReader = myCMD.ExecuteQuery()

it works ok but no results are returned. I've verified that the procedure returns results for the user I'm logged in as. When the query was executing I could see a refcursor in there but it was empty. I must be going mad.

Any help is appreciated

Ok I am an idiot, please ignore this question. The code above is fine and works perfectly, I simplified my code when I put it in here to make it easier for everyone to read, then just tested the simplified code and it works so I know where the problem is now and can start narrowing down. — Ralph
– Ralph, Commented Mar 28, 2011 at 0:15
So for anyone else that may have this problem and rage at the lack of an answer, I was passing the OracleDBType.Varchar2 as a parameter to the above VB method. But I had it declared as an integer, it needs to be explicitly passed as an OracleDBType. — Ralph
– Ralph, Commented Mar 28, 2011 at 1:00

Ralph · Accepted Answer · 2011-05-10 03:51:03Z

1

Anyone else that may have this problem, I was passing the OracleDBType.Varchar2 as a parameter to the above VB method. But I had it declared as an integer, it needs to be explicitly passed as an OracleDBType

answered May 10, 2011 at 3:51

Ralph

4786 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

ODP.NET VB.Net calling a stored procedure and returning a refCursor

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