Re: Returning a Value From a Stored Procedure using ADO

yes, with ADO you must use ref cursor to return vaiues.

"Daniel Morgan" <danmorg_at_sc.rr.com> wrote in message news:UCwQ6.56261$i56.17190773_at_typhoon.southeast.rr.com...
> I am using Visual Basic 6 and ADO (ActiveX Data Objects) to connect to an
> Oracle 8i database (8.1.6 to be exact). I'm using Oracle's OLE DB
 Provider,
> but willing to use Microsoft's OLE DB Provider or ODBC for Oracle if the
> need arises.
>
> My question, how do I return a value from an Oracle stored procedure or
> function if I'm using ADO and VB6?
>
> I have an example of my stored function returning a value:
>
> CREATE OR REPLACE SF_FOO ( pi_strSomething IN VARCHAR2)
> RETURN NUMBER
> AS
> v_nValueToReturn := 5;
> ....
> BEGIN
> ...
> RETURN v_nValueToReturn;
> END SF_FOO;
>
> Here is an example of my stored procedure returning a value:
>
> CREATE OR REPLACE SP_FOO (
> pi_strSomething IN VARCHAR2,
> po_nSomethingReturned OUT NUMBER )
> AS
> ...
> BEGIN
> ...
> po_nSomethingReturned := 5;
> END SP_FOO;
>
> And below, I have an example using VB6 and ADO to call an Oracle stored
> procedure that takes a parameter and returns a parameter.
>
> Dim DbCon As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim param1, param2 As ADODB.Parameter
> Dim rs As ADODB.Recordset
> Dim sSomething As String
>
> DbCon.ConnectionString = "Provider=OraOLEDB.Oracle; User ID=" & sUser &
 ";"
> & _
> "Password=" & sPassword & ";Data Source=" &
> sDatabase & ";"
>
> DbCon.Open
>
> sSomething = "ABC" ' ** arbitrary data to pass by value to the stored
> proc **
>
> Set cmd.ActiveConnection = DbCon
> cmd.CommandText = "{CALL sp_foo(?,?)}"
>
> Set param1 = cmd.CreateParameter( "param1", adVarChar, adParamInput,
 3 )
> cmd.Parameters.Append param1
> param1.Value = sSomething
>
> Set param2 = cmd.CreateParameter( "param2", adInteger, adParamOutput )
> cmd.Parameters.Append param2
>
> Set rs = cmd.Execute ' ** locks up cold here **
>
> If Not rs.EOF Then
> nAntKey = rs.Fields("param2").Value
> MsgBox "Returned Value from stored procedure: " & Str$(nAntKey)
> End If
>
> rs.Close
> DbCon.Close
>
> The only documentation on this I have read mentions that Oracle returning
> rowsets or "REF CURSORS". Is this the only way I can return a value?
 I'm
> not interested in returning a rowset - just a primitive value, such as, a
> number, string, date, etc...
>
> By the way, the code I'm using above locks up my application cold - I have
> to end the task in task manager.
>
> Thanks in advance,
> Daniel
>
>
>
Received on Tue May 29 2001 - 09:29:38 CDT