DevOps, CI/CD and Automation

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