This discussion is archived
2 Replies Latest reply: Apr 25, 2012 1:12 AM by 932305 RSS

Returning a ref cursor from oracle stored function to

932305 Newbie
Currently Being Moderated

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?


My code below: code

Private Sub GetAllProdLocs(ByVal dbConn As Oracle.DataAccess.Client.OracleConnection, ByVal dbTran As Oracle.DataAccess.Client.OracleTransaction)
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

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)
End If

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;

Open myRefCursor for Select pl.lid, l.maxqty,, p.pname, pl.qty
from a2prodloc pl
inner join a2loc l
on pl.lid = l.lid
inner join a2prod p
on =;

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


  • Correct Answers - 10 points
  • Helpful Answers - 5 points