Call Oracle Function from Visual Basic

Question

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

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:

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:

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.

Any ideas?

Answer 1

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

Answer 2

You must specify the (max) size of return value, i.e.

paramOracle.Size = 200