retrieving return value from oracle stored procedure 
Author Message
 retrieving return value from oracle stored procedure

hi everybody!

i'm having a stored procedure (dok_id.id_new) returning a varchar2(10)
value in an oracle-database.

now i'm trying to retrieve that value using an ado command object,
code as follows:

   Dim dbcGetDocID As New ADODB.Connection
   Dim cmdGetDocID As New ADODB.Command
   Dim Ret As String

   ' connect to db.....

   ' call procedure.

   With cmdGetDocID
      Set .ActiveConnection = dbcGetDocID

      .CommandType = adCmdStoredProc
      .CommandText = "dok_id.id_new"
      .Parameters.Append .CreateParameter(, adVarChar,
adParamReturnValue, 10, Ret)
      .Execute
   End With

no error occurs, but also no value is returned (Ret still = "").

can anybody help me.

thanks in advance,
christian



Sat, 09 Aug 2003 18:41:16 GMT  
 retrieving return value from oracle stored procedure
Since it seems that you are using a stored function, take a look at the code
below.

 CREATE OR REPLACE FUNCTION DOUBLE_NUMBER(P IN NUMBER) RETURN NUMBER IS
 BEGIN
   RETURN P*2;
 END;
 /

   Dim objCommand As ADODB.Command
   Dim objParameter As ADODB.Parameter

   Set objCommand = New Command

   Set objCommand.ActiveConnection = m_objADOConnection
   objCommand.CommandText = "{ ? = CALL DOUBLE_NUMBER(?) }"    ' ODBC syntax

   Set objParameter = objCommand.CreateParameter("P1", adDouble,
adParamOutput)
   objCommand.Parameters.Append objParameter
   Set objParameter = objCommand.CreateParameter("P2", adDouble,
adParamInput)
   objCommand.Parameters.Append objParameter
   Set objParameter = Nothing

   objCommand.Parameters("P2").Value = 4

   Call objCommand.Execute(Options:=adExecuteNoRecords And adCmdStoredProc)

   MsgBox objCommand.Parameters("P1").Value

   Set objCommand = Nothing

--
Best regards,

Carlos J. Quintero

Freeware "all-in-one" Add-In for VB6: TabIndex Assistant, Procedure Callers,
Find in All Projects, Close Windows, Review TabIndex, Review Collections,
Add Property or Function, Add Error Handler, Add Header Comment, Clear
Immediate Window, Project Statistics:
www.mztools.com



Quote:
> hi everybody!

> i'm having a stored procedure (dok_id.id_new) returning a varchar2(10)
> value in an oracle-database.

> now i'm trying to retrieve that value using an ado command object,
> code as follows:

>    Dim dbcGetDocID As New ADODB.Connection
>    Dim cmdGetDocID As New ADODB.Command
>    Dim Ret As String

>    ' connect to db.....

>    ' call procedure.

>    With cmdGetDocID
>       Set .ActiveConnection = dbcGetDocID

>       .CommandType = adCmdStoredProc
>       .CommandText = "dok_id.id_new"
>       .Parameters.Append .CreateParameter(, adVarChar,
> adParamReturnValue, 10, Ret)
>       .Execute
>    End With

> no error occurs, but also no value is returned (Ret still = "").

> can anybody help me.

> thanks in advance,
> christian



Sat, 09 Aug 2003 22:50:34 GMT  
 retrieving return value from oracle stored procedure
thank you!

this was the hint i needed:

        MsgBox objCommand.Parameters("P1").Value

everything else i've coded worked well...

best regards,
christian



Sat, 09 Aug 2003 23:03:10 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. retrieving return value from oracle stored procedure

2. Retrieve return value from Oracle stored procedure using DAO?

3. Return value from Oracle stored procedure with VB5?

4. Returning a Value from an Oracle Stored Procedure

5. Oracle Stored Procedure return value

6. Return value and Output values from Stored Procedure

7. RDO Error while retrieving resultset from ORACLE stored procedure using VB5

8. Problem: retrieve resultsets from Oracle Stored Procedures

9. Retrieving PL/SQL table from Oracle stored procedure

10. Return recordset from Oracle Stored Procedure to VB.

11. Returning resultsets from Oracle stored procedures via ADO?

12. RETURNING RESULTS FROM ORACLE STORED PROCEDURES WITH MSODBC DRIVER

 

 
Powered by phpBB® Forum Software