Returning a Value From a Stored Procedure using ADO

From: Daniel Morgan <danmorg_at_sc.rr.com>
Date: Mon, 28 May 2001 18:17:24 GMT
Message-ID: <UCwQ6.56261$i56.17190773@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 Mon May 28 2001 - 13:17:24 CDT