none
ODP.net - Parameter setup for stored procedure RRS feed

  • Question

  • I have the following PL/SQL stored function in my Oracle 10g database:

     FUNCTION get_complete_alarm_list
          (sector_list_in IN VARCHAR2)
       RETURN Operations.Ref_Cursor
       IS
          cursor_var Operations.Ref_Cursor;
       BEGIN
    ....
    ....
     -----------------------------------------------------------------------------------------------------

    I'm using ODP.net functionality to set up the parameter in my VB.net app as follows:

    Dim pOracleCommand As New OracleCommand("get_complete_alarm_list", pConnection)
    pOracleCommand.CommandType = CommandType.StoredProcedure

    Dim pInputParameter As New OracleParameter("sector_list_in", OracleDbType.Varchar2)
    pInputParameter.Direction() = ParameterDirection.Input
    pInputParameter.Value = "Misc Text"
    pOracleCommand.Parameters.Add(pInputParameter)

    Dim pReturnParameter As New OracleParameter("@return_value", OracleDbType.RefCursor)
    pReturnParameter.Direction = ParameterDirection.ReturnValue
    pOracleCommand.Parameters.Add(pReturnParameter)

    Dim pOracleDataReader As OracleDataReader = pOracleCommand.ExecuteReader()

    ----------------------------------------------------------------------------------------------------

    This all works fine when I don't have an input parameter.  As soon as I add the input parameter (in bold above), I keep getting the following error at the ExecuteReader() line, "PLS-00306: wrong number or types of arguments in call to 'GET_COMPLETE_ALARM_LIST'"

    I must be doing something wrong when trying to add the input parameter.  Any ideas??? Thanks in advance.
    Thursday, September 20, 2007 6:01 PM

Answers

  • Figured out the problem:  Apparently the return parameter must be added to the command before the input parameter.  I did this and everything worked fine.
    Thursday, September 20, 2007 11:23 PM

All replies

  • When your Parameter Direction is Input you need ExecuteNonQuery not ExecuteReader, check the link below for details.  Hope this helps.

     

    http://www.oracle.com/technology/oramag/oracle/06-jan/o16odpnet.html

     

    Thursday, September 20, 2007 7:16 PM
  • No, I can't use ExecuteNonQuery because my function returns a cursor of data, which I need to receive in .NET as an OracleDataReader.  I read that article before, but it doesn't really apply.
    Thursday, September 20, 2007 8:07 PM
  • Per your post in the thread below, I would not use ADO.NET Command objects when I am creating a report even if SQL Server is the source of my data because Reporting Services rejects most code that it thinks is out of scope.  Your error is a generic PL/SQL error.  You could use two PL/SQL blocks or create a View in SQL Server to generate the report data.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2165324&SiteID=1

    Thursday, September 20, 2007 8:38 PM
  • I'm not using SQL Server...just an Oracle 10g database with ODP.net and the built in .NET ReportViewer object.  Besides, everything with generating my reports works fine. 

    I'm just trying to figure out how to provide an input parameter to an Oracle Database function using  ODP.net in my VB.net app.  Everything I've read online indicates I'm doing this right, but I can't figure out why I'm getting the error I described.
    Thursday, September 20, 2007 8:47 PM
  • (The LocalReport object does not have the ability to execute queries or fetch data; instead, data must be supplied to it as instances of ADO.NET DataTables or as a collection of business objects.

    The LocalReport object supports report parameters, but does not support query parameters. )

     

    The above says what you are doing is not valid, I would not have replied you if I had seen ReportViewer in your post because I know it is not valid.


    http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.localreport(VS.80).aspx

     

    Thursday, September 20, 2007 9:13 PM
  • Caddre:  I purposefully left the whole "ReportViewer" notion out of the original post because my problem has nothing to do with reports.  As stated before, I have the report end of my app working fine.  I'm just trying to bring in data to the app by connecting to an Oracle 10g database with ODP.net API.  That's where the problem lies...it has nothing to do with reports.  The code I provided works fine when I leave out the input parameter in both the VB.net and PL/SQL code.  When I add it though, I get the error.

    Thanks for your help on this so far.
    Thursday, September 20, 2007 9:30 PM
  • Figured out the problem:  Apparently the return parameter must be added to the command before the input parameter.  I did this and everything worked fine.
    Thursday, September 20, 2007 11:23 PM
  • Yes the problem is the order of the parameters. I put the Output parameter RefCursor first and begin to run ok.

    Very thanks ¡¡ I turnd crazy whith it

     

    Tuesday, October 4, 2011 11:32 AM