Como chamar uma função Oracle com um Cursor Ref e retornar os valores no Visual Basic [duplicado]

Question

Estou tentando chamar a função Oracle que retorna SYS_REFCURSOR do VB. Consegui recuperar dados usando procedimento armazenado. Da mesma forma tentei chamar a função oracle mas deu erro.

Agradeço se alguém puder ajudar? Obrigado. Fornecerei capturas de tela, se necessário.

Função na extremidade DB

PKG BODY

FUNCTION GET_ALLCUST RETURN SYS_REFCURSOR AS 
  getallcursor SYS_REFCURSOR; 
BEGIN
    OPEN getallcursor FOR SELECT * FROM CUSTOMER; 
    RETURN getallcursor; 
CLOSE getallcursor;
END;

PKG Spec
==============

create or replace PACKAGE GetAllProdPckg AS
   PROCEDURE GetAllProd;
   PROCEDURE get_allprod_viasqldev;
   PROCEDURE get_allcust_viasqldev;
   FUNCTION get_allcust RETURN SYS_REFCURSOR;
   function GET_ALLPROD_FROM_DB RETURN SYS_REFCURSOR;
   
   END GetAllProdPckg;

MEU Código ====================================================== ===========================

Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        Dim rvConn As Oracle.DataAccess.Client.OracleConnection
        rvConn = CreateConnection()
        Dim rvCmd As New Oracle.DataAccess.Client.OracleCommand
        '  Dim OracleRefCursor As New Oracle.DataAccess.Client.OracleDbType.RefCursor
        Dim ds As DataSet

        Dim oraDataAdapter As New Oracle.DataAccess.Client.OracleDataAdapter()


        Try
            rvCmd.Connection = rvConn
            rvConn.Open()

            Dim strSQL As String
            strSQL = "getallprodpckg.get_allcust"
            rvCmd.CommandType = CommandType.StoredProcedure
            rvCmd.CommandText = strSQL
            MsgBox("")

            Dim qrySerial3 As New Oracle.DataAccess.Client.OracleParameter("getallpcursor", OracleDbType.RefCursor) With {
            .Direction = ParameterDirection.ReturnValue}


            rvCmd.Parameters.Add(qrySerial3)


            Dim vStr As String
            Dim reader As OracleDataReader = rvCmd.ExecuteReader

            While reader.Read()
                Console.WriteLine("{0}", reader(0) & reader(1) & reader(2))
                MsgBox(vStr)
            End While


        Catch ex As Exception
            MessageBox.Show("ERROR OCCURRED" & ex.Message)

        Finally
            rvConn.Close()
        End Try

    End Sub

O código acima retorna um valor quando eu chamo o procedimento armazenado com o objeto cursor. O cursor basicamente retorna todos os dados de uma tabela. Mas quando eu tento em uma função, ele lança uma exceção, o código que tentei está abaixo. ** ERROR ** System.NullReferenceException: 'Referência de objeto não definida para uma instância de um objeto.

Exceção lançada

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        Dim rvConn As Oracle.DataAccess.Client.OracleConnection
        rvConn = CreateConnection()
        Dim rvCmd As New Oracle.DataAccess.Client.OracleCommand
        '  Dim OracleRefCursor As New Oracle.DataAccess.Client.OracleDbType.RefCursor
        Dim ds As DataSet

        Dim oraDataAdapter As New Oracle.DataAccess.Client.OracleDataAdapter()


        Try
            rvCmd.Connection = rvConn
            rvConn.Open()

            Dim strSQL As String
            strSQL = "select getallprodpckg.get_allcust() from dual"
            rvCmd.CommandType = CommandType.Text
            rvCmd.CommandText = strSQL
            MsgBox("")

            Dim qrySerial2 As New Oracle.DataAccess.Client.OracleParameter("getallpcursor", OracleDbType.RefCursor) With {
            .Direction = ParameterDirection.ReturnValue}


            rvCmd.Parameters.Add(qrySerial2)


            Dim vStr As String
            Dim reader As OracleDataReader = rvCmd.ExecuteReader

            While reader.Read()
                Console.WriteLine("{0}", reader(0) & reader(1) & reader(2))
                MsgBox(vStr)
            End While


        Catch ex As Exception
            MessageBox.Show("ERROR OCCURRED" & ex.Message)

        Finally
            rvConn.Close()
        End Try

    End Sub
End Class

** Classe de conexão DB **

Public Class Form1 Private Sub TestOracleButton_Click(sender As Object, e As EventArgs) Handles TestOracleButton.Click TestOracleConnection() End Sub

Dim custID As Integer
Dim custID2 As Integer
Dim custName As String
Dim prodID As Integer
Dim prodName As String
Dim prodPrice As Integer
Dim prodAmount As Integer
Dim custAmount As Integer
Dim custStatus As String
Dim prodQuant As Integer

Public Sub TestOracleConnection()
    Dim rvConn As Oracle.DataAccess.Client.OracleConnection
    rvConn = CreateConnection()
    Try
        rvConn.Open()
        MessageBox.Show("Oracle Connection OK")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        MessageBox.Show("No Oracle Connection established")
    Finally
        rvConn.Close()
    End Try
End Sub
Public Function CreateConnection() As Oracle.DataAccess.Client.OracleConnection
    Dim rvConn As New Oracle.DataAccess.Client.OracleConnection With {
        .ConnectionString = GetConnectionString()
    }
    Return rvConn
End Function
Public Function GetConnectionString() As String
    Dim vConnStr As String
    vConnStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
    vConnStr = vConnStr & "(HOST=feenix-oracle.swin.edu.au)(PORT=1521))"
    vConnStr = vConnStr & "(CONNECT_DATA=(SERVICE_NAME=dms)));"
    vConnStr = vConnStr & "User Id=S1234564;"
    vConnStr = vConnStr & "Password=******;"
    Return vConnStr
End Function

Answer 1

Você precisa obter o leitor do parâmetro, não do comando.

Então, em vez de

Dim reader As OracleDataReader = rvCmd.ExecuteReader()

Você precisa:

rvCmd.CommandType = CommandType.StoredProcedure
rvCmd.CommandText = "getallprodpckg.get_allcust"

Dim qrySerial3 As New Oracle.DataAccess.Client.OracleParameter(
    None, OracleDbType.RefCursor, ParameterDirection.ReturnValue)

rvCmd.Parameters.Add(qrySerial3)

rvCmd.ExecuteNonQuery()

Dim cursor as OracleRefCursor = DirectCast(qrySerial3.Value, OracleRefCursor)

Dim reader As OracleDataReader = cursor.GetDataReader()