StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'll assume you are using ODP.net (native Oracle client for .net).</p> <p>Let's say you have 2 Oracle stored functions like this:</p> <pre><code> 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; </code></pre> <p>One of the functions returns a VARCHAR2 and the other returns ref cursor. On VB side, you could do this:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload