What is the right way to call an Oracle stored function from ado.net and get the result?

Question

I've got a vb.net codebase using ado to connect to an Oracle database. We have lots of stored procedures that we call, some with multiple out parameters. However, I now need to call a stored function, and it's not clear to me how to get the result of the function back into my VB code.

Edit: I'm returning an integer.

How do I properly call an oracle stored function from ado.net?

Answer 1

I'll assume you are using ODP.net (native Oracle client for .net).

Let's say you have 2 Oracle stored functions like this:

   FUNCTION my_func
   (
      p_parm1 VARCHAR2
    , p_parm2 NUMBER
   ) RETURN VARCHAR2
   AS
   BEGIN
      RETURN p_parm1 || to_char(p_parm2);
   END;

   FUNCTION my_func2 RETURN SYS_REFCURSOR
   AS
      v_cursor SYS_REFCURSOR;
   BEGIN
      OPEN v_cursor FOR
         SELECT 'hello there Sean' col1
           FROM dual
          UNION ALL
         SELECT 'here is your answer' col1
           FROM dual;      
      RETURN v_cursor;          
   END;

One of the functions returns a VARCHAR2 and the other returns ref cursor. On VB side, you could do this:

Dim con As New OracleConnection("Data Source=xe;User Id=sandbox;Password=sandbox; Promotable Transaction=local")

Try
    con.Open()
    Dim cmd As OracleCommand = con.CreateCommand()
    cmd.CommandText = "test_pkg.my_func"
    cmd.CommandType = CommandType.StoredProcedure

    Dim parm As OracleParameter

    parm = New OracleParameter()
    parm.Direction = ParameterDirection.ReturnValue
    parm.OracleDbType = OracleDbType.Varchar2
    parm.Size = 5000
    cmd.Parameters.Add(parm)

    parm = New OracleParameter()
    parm.Direction = ParameterDirection.Input
    parm.Value = "abc"
    parm.OracleDbType = OracleDbType.Varchar2
    cmd.Parameters.Add(parm)

    parm = New OracleParameter()
    parm.Direction = ParameterDirection.Input
    parm.Value = 42
    parm.OracleDbType = OracleDbType.Int32
    cmd.Parameters.Add(parm)

    cmd.ExecuteNonQuery()
    Console.WriteLine("result of first function is " + cmd.Parameters(0).Value)

    '''''''''''''''''''''''''''''''''''''''''''''
    ' now for the second query
    '''''''''''''''''''''''''''''''''''''''''''''
    cmd = con.CreateCommand()
    cmd.CommandText = "test_pkg.my_func2"
    cmd.CommandType = CommandType.StoredProcedure

    parm = New OracleParameter()
    parm.Direction = ParameterDirection.ReturnValue
    parm.OracleDbType = OracleDbType.RefCursor
    cmd.Parameters.Add(parm)

    Dim dr As OracleDataReader = cmd.ExecuteReader()
    While (dr.Read())
        Console.WriteLine(dr(0))
    End While

Finally
    If (Not (con Is Nothing)) Then
        con.Close()
    End If
End Try

Answer 2

' Create ODP database connection 
Dim constr As String = (("User Id=" & Properties.Settings.[Default].DbUid & "; Password=") + Properties.Settings.[Default].DbPwd & "; Data Source=") + Properties.Settings.[Default].DbTnsName & "; Pooling=false"
Dim con As New OracleConnection(constr)
Dim cmd As New OracleCommand()
cmd.Connection = con

Try
    cmd.CommandText = "test_pkg.test_function"
    cmd.CommandType = CommandType.StoredProcedure

    'Always add return parameter before other parameters when calling database functions in .net!!
    cmd.Parameters.Add("result", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue
    cmd.Parameters.Add("param1", OracleDbType.Varchar2).Value = "something"
    cmd.Parameters.Add("param2", OracleDbType.Varchar2).Value = "something else"
    con.Open()
    cmd.ExecuteNonQuery()

    Console.WriteLine(CInt(cmd.Parameters("result").Value))
Finally
    ' Cleanup
    con.Close()
    cmd.Dispose()
    con.Dispose()
End Try