从Visual Basic调用Oracle函数 [英] Call Oracle Function from Visual Basic

问题描述

不确定为什么我的代码在从VB调用时返回错误，但是PLSQL（Oracle）中类似的代码不会返回错误。

Unsure why my code is returning an error when called from VB, however similar code in PLSQL (Oracle) isn't returning the error.

Oracle函数： / p>

Oracle Function:

CREATE OR REPLACE FUNCTION GET_CUST_STRING_FROM_DB (pcustid NUMBER) RETURN VARCHAR2 AS
    returnstring VARCHAR2(200);
    vcustomer customer%rowtype;
BEGIN
    SELECT * INTO vcustomer
    FROM customer
    WHERE custid = pcustid;

    returnstring := 'CustID: ' || vcustomer.custid || ' Name: ' || vcustomer.custname || ' Status: ' || vcustomer.status || ' SalesYTD: ' || vcustomer.sales_ytd;
    RETURN returnstring;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20021,'Error: Customer ID not found');
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END;
/

Oracle Call：

Oracle Call:

set serveroutput on;

begin
    dbms_output.put_line(get_cust_string_from_db(1));
end;

Oracle输出：

Anonymous Block Completed
CustID: 1 Name: Colin Smith Status: OK SalesYTD: 0

VB代码：

Private Sub GetCustString()
    Try
        Dim connOracle As Oracle.DataAccess.Client.OracleConnection
        Dim commOracle As New Oracle.DataAccess.Client.OracleCommand
        Dim paramOracle As Oracle.DataAccess.Client.OracleParameter

        connOracle = CreateConnection()
        commOracle.Connection = connOracle
        commOracle.CommandType = CommandType.StoredProcedure
        commOracle.CommandText = "GET_CUST_STRING_FROM_DB"

        paramOracle = New Oracle.DataAccess.Client.OracleParameter
        paramOracle.ParameterName = "pcustid"
        paramOracle.DbType = DbType.Int16
        paramOracle.Value = tbGetCustStringCustID.Text
        paramOracle.Direction = ParameterDirection.Input
        commOracle.Parameters.Add(paramOracle)

        paramOracle = New Oracle.DataAccess.Client.OracleParameter
        paramOracle.ParameterName = "pReturnValue"
        paramOracle.DbType = DbType.String
        paramOracle.Direction = ParameterDirection.ReturnValue
        commOracle.Parameters.Add(paramOracle)

        lbOutput.Items.Add(commOracle.Parameters.Item("pcustid").Value)
        lbOutput.Items.Add("--------------------------------------------")
        lbOutput.Items.Add("Getting Details for CustID " & tbGetCustStringCustID.Text)

        connOracle.Open()
        commOracle.ExecuteNonQuery()
        Dim vStr As String
        vStr = commOracle.Parameters.Item("pReturnValue").Value.ToString
        lbOutput.Items.Add(vStr)
        connOracle.Close()
    Catch ex As Exception
        lbOutput.Items.Add(ex.Message)
    End Try
End Sub

VB输出：

1
-----------------------------------------
Getting Details for CustID 1
ORA-20021: Error: Customer ID not found

所以我不确定为什么会发生这种情况。
我试图捕获参数的值，并确保它是一个实际的整数，而不是一个字符串。

So i'm unsure as to why this might happen. I tried catching the value of the parameter and ensuring it was an actual integer and not a string. The CustID 1 does exist in the database and is committed.

任何想法？

推荐答案

除了指定返回值的大小之外，您还需要先添加返回值参数...

As well as specifying ths size of the return value you need to add the return value parameter first...

Private Sub GetCustString()
    Try
        Dim connOracle As Oracle.DataAccess.Client.OracleConnection
        Dim commOracle As New Oracle.DataAccess.Client.OracleCommand
        Dim paramOracle As Oracle.DataAccess.Client.OracleParameter

        connOracle = CreateConnection()
        commOracle.Connection = connOracle
        commOracle.CommandType = CommandType.StoredProcedure
        commOracle.CommandText = "GET_CUST_STRING_FROM_DB"

        paramOracle = New Oracle.DataAccess.Client.OracleParameter
        paramOracle.ParameterName = "pReturnValue"
        paramOracle.DbType = DbType.String
        paramOracle.Size = 200
        paramOracle.Direction = ParameterDirection.ReturnValue
        commOracle.Parameters.Add(paramOracle)

        paramOracle = New Oracle.DataAccess.Client.OracleParameter
        paramOracle.ParameterName = "pcustid"
        paramOracle.DbType = DbType.Int16
        paramOracle.Value = tbGetCustStringCustID.Text
        paramOracle.Direction = ParameterDirection.Input
        commOracle.Parameters.Add(paramOracle)

        lbOutput.Items.Add(commOracle.Parameters.Item("pcustid").Value)
        lbOutput.Items.Add("--------------------------------------------")
        lbOutput.Items.Add("Getting Details for CustID " & tbGetCustStringCustID.Text)

        connOracle.Open()
        commOracle.ExecuteNonQuery()
        Dim vStr As String
        vStr = commOracle.Parameters.Item("pReturnValue").Value.ToString
        lbOutput.Items.Add(vStr)
        connOracle.Close()
    Catch ex As Exception
        lbOutput.Items.Add(ex.Message)
    End Try
End Sub

这篇关于从Visual Basic调用Oracle函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！