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?
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