Hi,

I am trying to return a ref cursor from a stored function but I keep getting this error message 'Specified argument was out of range of valid values'. The reference cursor does point to correct data, I used a anonymous block in oracle to test it out.

How can I fix the problem?

Thanks

My code below:

vb.net code
Code:
    Private Sub GetAllProdLocs(ByVal dbConn As Oracle.DataAccess.Client.OracleConnection, ByVal dbTran As Oracle.DataAccess.Client.OracleTransaction)
        Try
            Dim dbCmd As New Oracle.DataAccess.Client.OracleCommand
            Dim param1 As New Oracle.DataAccess.Client.OracleParameter
            Dim readerOracle As Oracle.DataAccess.Client.OracleDataReader = Nothing

            dbCmd.Connection = dbConn
            dbCmd.Transaction = dbTran
            dbCmd.CommandText = "a2Package1.a2GetAllProdLocs"
            dbCmd.CommandType = CommandType.StoredProcedure

            param1.ParameterName = "myRefCursor"
            param1.DbType = Oracle.DataAccess.Client.OracleDbType.RefCursor
            param1.Direction = ParameterDirection.ReturnValue
            dbCmd.Parameters.Add(param1)

            dbCmd.ExecuteNonQuery()
            readerOracle = dbCmd.Parameters("myRefCursor").Value
            'If readerOracle.HasRows = True Then
            'Me.ListBox1.Items.Add("Location ID      Max Quantity    Product ID      Product Name        Quantity")
            'Do While readerOracle.Read()
            'ListBox1.Items.Add(readerOracle("lid")) '& "   " & readerOracle("maxqty") & "      " & readerOracle("pid") & "     " & readerOracle("pname") & "    " & readerOracle("qty") & Environment.NewLine)
            'Loop

            'End If
            readerOracle.Close()
        Catch ex As Oracle.DataAccess.Client.OracleException
            Throw ex
        End Try
    End Sub
Oracle code
Code:
/
Create or replace package a2Package1 is
  Type refCursor is ref cursor;
  Function a2GetAllProdLocs Return refCursor;
  Function a2GetAllAudit return refCursor;
End a2Package1;
/
/
Create or replace package body a2Package1 is
  Function a2GetAllProdLocs return refCursor is
    myRefCursor refCursor;
  Begin
    Open myRefCursor for Select pl.lid, l.maxqty, pl.pid, p.pname, pl.qty
                         from a2prodloc pl
                         inner join a2loc l
                         on pl.lid = l.lid
                         inner join a2prod p
                         on pl.pid = p.pid;
    return myRefCursor;
  End a2GetAllProdLocs;

  Function a2GetAllAudit return refCursor is
    myRefCursor refCursor;
  Begin
    Open myRefCursor for select aid, atype, fromloc, toloc, pid, qty, to_char(adate, 'YYYY/MM/DD HH:MI:SS')
            from a2audit
            order by aid asc;
    return myRefCursor;
  End a2GetAllAudit;
End a2Package1;
/