vb.net 从 Visual Basic 调用 Oracle 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23453347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call Oracle Function from Visual Basic
提问by user3600760
Unsure why my code is returning an error when called from VB, however similar code in PLSQL (Oracle) isn't returning the error.
不确定为什么我的代码在从 VB 调用时返回错误，但是 PLSQL (Oracle) 中的类似代码没有返回错误。
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:
甲骨文调用：
set serveroutput on;
begin
dbms_output.put_line(get_cust_string_from_db(1));
end;
Oracle Output:
甲骨文输出：
Anonymous Block Completed
CustID: 1 Name: Colin Smith Status: OK SalesYTD: 0
VB Code:
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 Output:
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.
所以我不确定为什么会发生这种情况。我尝试捕捉参数的值并确保它是一个实际的整数而不是一个字符串。CustID 1 确实存在于数据库中并已提交。
Any ideas?
有任何想法吗？
回答by Ciarán
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
回答by Wernfried Domscheit
You must specify the (max) size of return value, i.e.
您必须指定返回值的（最大）大小，即
paramOracle.Size = 200
