2 Replies Latest reply: Apr 25, 2012 3:12 AM by 932305 RSS

    Returning a ref cursor from oracle stored function to vb.net

    932305
      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

      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

      Create or replace package a2Package1 is

      Type refCursor is ref cursor;
      Function a2GetAllProdLocs 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;

      End a2Package1;

      Edited by: user4153911 on Apr 21, 2012 5:42 PM

      Edited by: user4153911 on Apr 21, 2012 5:50 PM

      Edited by: user4153911 on Apr 21, 2012 5:51 PM