none - 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
          cursor_var Operations.Ref_Cursor;

    I'm using functionality to set up the parameter in my 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"

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

    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


  • 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.



    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.


    Thursday, September 20, 2007 8:38 PM
  • I'm not using SQL Server...just an Oracle 10g database with 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 in my 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.


    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 API.  That's where the problem has nothing to do with reports.  The code I provided works fine when I leave out the input parameter in both the 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