如何使用 Ref Cursor 调用 Oracle 函数并在 Visual Basic 上返回值 [英] How to call an Oracle function with a Ref Cursor and return the values on Visual Basic

问题描述

我正在尝试调用从 VB 返回 SYS_REFCURSOR 的 Oracle 函数.我能够使用存储过程检索数据.我尝试以同样的方式调用 oracle 函数，但它给出了错误.

感谢有人能帮忙吗?谢谢.如果需要，我会提供屏幕截图.

DB端函数

PKG BODY函数 GET_ALLCUST 返回 SYS_REFCURSOR ASgetallcursor SYS_REFCURSOR;开始OPEN getallcursor FOR SELECT * FROM CUSTOMER;返回 getallcursor;关闭 getallcursor;结尾;包装规格==============创建或替换 PACKAGE GetAllProdPckg AS程序 GetAllProd;程序 get_allprod_viasqldev;程序 get_allcust_viasqldev;函数 get_allcust RETURN SYS_REFCURSOR;函数 GET_ALLPROD_FROM_DB RETURN SYS_REFCURSOR;结束 GetAllProdPckg;

我的代码 ============================================================================

<预><代码>Private Sub Button1_Click_1(sender As Object, e As EventArgs) 处理Button1.Click将 rvConn 调暗为 Oracle.DataAccess.Client.OracleConnectionrvConn = 创建连接()Dim rvCmd 作为新的 Oracle.DataAccess.Client.OracleCommand' Dim OracleRefCursor As New Oracle.DataAccess.Client.OracleDbType.RefCursorDim ds 作为数据集Dim oraDataAdapter As New Oracle.DataAccess.Client.OracleDataAdapter()尝试rvCmd.Connection = rvConnrvConn.Open()Dim strSQL As StringstrSQL = "getallprodpckg.get_allcust";rvCmd.CommandType = CommandType.StoredProcedurervCmd.CommandText = strSQLMsgBox("")Dim qrySerial3 As New Oracle.DataAccess.Client.OracleParameter("getallpcursor", OracleDbType.RefCursor) 与 {.Direction = ParameterDirection.ReturnValue}rvCmd.Parameters.Add(qrySerial3)将 vStr 调暗为字符串Dim reader As OracleDataReader = rvCmd.ExecuteReader而 reader.Read()Console.WriteLine({0}", reader(0) & reader(1) & reader(2))MsgBox(vStr)结束时间Catch ex 作为例外MessageBox.Show(发生错误"& ex.Message)最后rvConn.Close()结束尝试结束子

当我使用游标对象调用存储过程时，上述代码确实返回了一个值.游标基本上返回表中的所有数据.但是当我在函数上尝试它时会引发异常，我尝试的代码如下.** 错误 **System.NullReferenceException: '未将对象引用设置为对象的实例.

抛出异常

<预><代码>Private Sub Button1_Click_1(sender As Object, e As EventArgs) 处理Button1.Click将 rvConn 调暗为 Oracle.DataAccess.Client.OracleConnectionrvConn = 创建连接()Dim rvCmd 作为新的 Oracle.DataAccess.Client.OracleCommand' Dim OracleRefCursor As New Oracle.DataAccess.Client.OracleDbType.RefCursorDim ds 作为数据集Dim oraDataAdapter As New Oracle.DataAccess.Client.OracleDataAdapter()尝试rvCmd.Connection = rvConnrvConn.Open()Dim strSQL As StringstrSQL = "select getallprodpckg.get_allcust() from dual";rvCmd.CommandType = CommandType.TextrvCmd.CommandText = strSQLMsgBox("")Dim qrySerial2 As New Oracle.DataAccess.Client.OracleParameter("getallpcursor", OracleDbType.RefCursor) 与 {.Direction = ParameterDirection.ReturnValue}rvCmd.Parameters.Add(qrySerial2)将 vStr 调暗为字符串Dim reader As OracleDataReader = rvCmd.ExecuteReader而 reader.Read()Console.WriteLine({0}", reader(0) & reader(1) & reader(2))MsgBox(vStr)结束时间Catch ex 作为例外MessageBox.Show(发生错误"& ex.Message)最后rvConn.Close()结束尝试结束子结束类

** 数据库连接类 **

<预><代码>公共类 Form1 Private Sub TestOracleButton_Click(sender As Object, e As EventArgs) 处理 TestOracleButton.Click TestOracleConnection() End Sub将 custID 调暗为整数将 custID2 调暗为整数Dim custName As String将 prodID 调暗为整数Dim prodName As StringDim prodPrice 作为整数将 prodAmount 变暗为整数将 custAmount 调暗为整数Dim custStatus As StringDim prodQuant 作为整数公共子测试OracleConnection()将 rvConn 调暗为 Oracle.DataAccess.Client.OracleConnectionrvConn = 创建连接()尝试rvConn.Open()MessageBox.Show(Oracle 连接正常")Catch ex 作为例外MessageBox.Show(ex.Message)MessageBox.Show(未建立 Oracle 连接")最后rvConn.Close()结束尝试结束子公共函数 CreateConnection() 作为 Oracle.DataAccess.Client.OracleConnectionDim rvConn 作为新的 Oracle.DataAccess.Client.OracleConnection with {.ConnectionString = GetConnectionString()}返回 rvConn结束函数公共函数 GetConnectionString() As String将 vConnStr 调暗为字符串vConnStr = "数据源=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP))";vConnStr = vConnStr &(HOST=feenix-oracle.swin.edu.au)(PORT=1521))"vConnStr = vConnStr &"(CONNECT_DATA=(SERVICE_NAME=dms)));"vConnStr = vConnStr &用户 ID=S1234564；"vConnStr = vConnStr &"密码=*****;"返回 vConnStr结束函数

解决方案

你需要从参数中获取读取器，而不是从命令中获取.

所以代替

Dim reader As OracleDataReader = rvCmd.ExecuteReader()

您需要:

rvCmd.CommandType = CommandType.StoredProcedurervCmd.CommandText = "getallprodpckg.get_allcust";Dim qrySerial3 As New Oracle.DataAccess.Client.OracleParameter(无、OracleDbType.RefCursor、ParameterDirection.ReturnValue)rvCmd.Parameters.Add(qrySerial3)rvCmd.ExecuteNonQuery()Dim cursor as OracleRefCursor = DirectCast(qrySerial3.Value, OracleRefCursor)Dim reader As OracleDataReader = cursor.GetDataReader()

I am trying to call Oracle function which return SYS_REFCURSOR from VB. I was able to retrieve data using stored procedure. Same way I tried to call oracle function but it giving an error.

Appreaciate if anyone can help ? Thanks. I'll provide screen shots if needed.

Function in DB end

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;

MY Code =========================================================================

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

The Above Code does return a value when i call the Stored Procedure with the cursor object. The cursor basically returns all data from a table. But When i Try it on a Function it throws an exception, The code i tried is below. ** ERROR ** System.NullReferenceException: 'Object reference not set to an instance of an object.

Exception Thrown

    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

** DB connection class **

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

解决方案

You need to get the reader from the parameter, not from the command.

So instead of

Dim reader As OracleDataReader = rvCmd.ExecuteReader()

You need:

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()

这篇关于如何使用 Ref Cursor 调用 Oracle 函数并在 Visual Basic 上返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！