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;
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;
/