ADO, Oracle, VB6 and stored procedures - ugly mix

[Mystic1112]

Hello. If you can help me with a link to an explicit example or a snippet of code, I would appreciate it. I have an application that needs to call an Oracle stored procedure and pass several parameters for both input and output. One of the output parameters is a recordset type (pl/sql table) and I am having trouble getting a matching parameter definition because ADO doesn't appear to have an equivalent type for the parameter.

The procedure definition is as follows:

PROCEDURE getCustomerInfo(call_id IN VARCHAR2, accountNum IN NUMBER, o_call_id OUT VARCHAR2, o_status OUT NUMBER, o_getAccountInfoTbl OUT GetAccountInfoTbl);

The code I use to call the procedure is as follows:

Set cmd.ActiveConnection = conn
cmd.CommandText = "{ call sp_stored_procedure(?, {resultset 100, o_call_id, o_status, o_getAccountInfoTbl})}"
cmd.CommandType = adCmdText
cmd.Parameters(0).Value = "10000"
cmd.Execute

Again, any suggestions would be wonderful.

Thanks,
Mystic

 

[Mystic1112]

Oops, on the cmd.execute line it should read

set rs = cmd.execute

Thanks.

 

[fredericofonseca]

two examples from metalink.
They may or not be what you need.

Example 1 -------

' Create the types and package as follows:
'
' DROP TYPE empdetails;
' DROP TYPE myemprec;
'
' CREATE TYPE myemprec AS OBJECT (ename VARCHAR2(10), deptno INTEGER);
' /
' CREATE TYPE empdetails AS TABLE OF myemprec
' /
' CREATE OR REPLACE PACKAGE adotst
' AS
'   TYPE empcur IS REF CURSOR;
'   PROCEDURE getemps(vdeptno IN NUMBER, vcount OUT NUMBER, empc OUT empcur);
' END adotst;
' /
'
' CREATE OR REPLACE PACKAGE BODY adotst
' AS
'   PROCEDURE getemps(vdeptno IN NUMBER, vcount OUT NUMBER, empc OUT EMPCUR)
'   AS
'     ecur         adotst.empcur;
'     emprec_temp  myemprec := myemprec(null,null);
'     emptable     empdetails;
'   BEGIN      
'     emptable := empdetails(null);
'     emptable.Trim;
'
'     -- Put first row into table    
'     emprec_temp.ename  := 'SMITH';
'     emprec_temp.deptno :=  20;
'     emptable.Extend;
'     emptable(emptable.Last) := emprec_temp;
'
'     -- Put second row into table    
'     emprec_temp.ename  := 'JONES';
'     emprec_temp.deptno :=  20;
'     emptable.Extend;
'     emptable(emptable.Last) := emprec_temp;
'   
'     -- Put third row into table    
'     emprec_temp.ename  := 'ALLEN';
'     emprec_temp.deptno :=  30;
'     emptable.Extend;
'     emptable(emptable.Last) := emprec_temp;
'   
'     OPEN ecur FOR
'       SELECT ename
'       FROM table (CAST (emptable AS empdetails) ) t
'       WHERE t.deptno = vdeptno
'       ORDER BY t.ename;
'
'     vcount := SQL%ROWCOUNT;
'     empc := ecur;
'
'   END getemps;
' END adotst;
' /


Private Sub Command1_Click()

  Dim cnn1 As ADODB.Connection
  Dim cmdExeproc As ADODB.Command
  Dim prmDeptno As ADODB.Parameter
  Dim prmECount As ADODB.Parameter
  Dim rstEmps As ADODB.Recordset
  Dim intDeptno As Integer
  Dim strEname As String
  Dim strCnn As String

  ' Open connection.

  Set cnn1 = New ADODB.Connection

  ' Modify the following line to reflect a DSN within your environment

  strCnn = "DSN=local; UID=scott; PWD=tiger;"
  cnn1.Open strCnn
  cnn1.CursorLocation = adUseClient

  ' Open command object with one parameter.

  Set cmdExeproc = New ADODB.Command

  ' Note that this has been tested using
  ' cmdExeproc.CommandText = "scott.adotst.GetEmps"
  ' which also works

  cmdExeproc.CommandText = "adotst.GetEmps"
  cmdExeproc.CommandType = adCmdStoredProc

  ' Get parameter value and append parameter.

  intDeptno = Trim(InputBox("Enter Department:"))
  Set prmDeptno = cmdExeproc.CreateParameter("vdeptno", _
    adInteger, adParamInput)
  Set prmECount = cmdExeproc.CreateParameter("vcount", _
    adInteger, adParamOutput)
  cmdExeproc.Parameters.Append prmDeptno
  prmDeptno.Value = intDeptno
  cmdExeproc.Parameters.Append prmECount

  ' Create recordset by executing the command.

  Set cmdExeproc.ActiveConnection = cnn1
  Set rstEmps = cmdExeproc.Execute

  ' Build string to be displayed with information returned

  strEname = "The " & prmECount.Value & _
    " Employees in Department " & intDeptno & " are :"

  Do While Not rstEmps.EOF
    strEname = strEname & " " & rstEmps!ename & ","
    rstEmps.MoveNext
  Loop

  MsgBox (strEname)

  ' Close resultsets and log off

  rstEmps.Close
  cnn1.Close

End Sub

example 2 ---------

Execution Environment:
     Visual Basic 6.0

Access Privileges:
     Requires the ability to create and execute stored procedures.

Usage:
     Run the console application from a command prompt or from within Visual Basic.

Instructions:
     1.  Set up the following PL/SQL package in SQL*Plus:
     
         CREATE OR REPLACE PACKAGE retcursor AS
           TYPE tempno is TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;
           TYPE tename is TABLE of VARCHAR2(10) INDEX BY BINARY_INTEGER;
           TYPE tjob is TABLE of VARCHAR2(9) INDEX BY BINARY_INTEGER;
           PROCEDURE allemp (empno OUT tempno, ename OUT tename, job OUT tjob);
         PROCEDURE oneemp (oneempno IN NUMBER, empno OUT tempno,ename OUT tename, job OUT tjob);
         END retcursor;
         /
         
     2.  Set up the following package body in SQL*Plus:
     
         CREATE OR REPLACE PACKAGE BODY retcursor AS
           PROCEDURE allemp(empno OUT tempno, ename OUT tename, job OUT tjob) IS
             CURSOR emp_cur IS SELECT empno, ename, job FROM emp;
             empcount NUMBER DEFAULT 1;
             BEGIN
               FOR singleemp IN emp_cur LOOP
                 empno(empcount) := singleemp.empno;
                 ename(empcount) := singleemp.ename;
                 job(empcount) := singleemp.job;
                 empcount := empcount + 1;
               END LOOP;
             END;
           PROCEDURE oneemp(oneempno IN NUMBER, empno OUT tempno, ename OUT tename, job OUT tjob) IS
             CURSOR emp_cur IS SELECT empno, ename, job FROM emp WHERE empno = oneempno;
             empcount NUMBER DEFAULT 1;
             BEGIN
               FOR singleemp IN emp_cur LOOP
                 empno(empcount) := singleemp.empno;
                 ename(empcount) := singleemp.ename;
                 job(empcount) := singleemp.job;
                 empcount := empcount + 1;
               END LOOP;
             END;
         END;
         /
         
     3.  Create three named buttons on your Visual Basic Form:
     
             cmdAll
             cmdOne
             cmdByeBye
             
         All returned data will be displayed with Message Boxes
         
     4.  This sample requires the use of the Microsoft ODBC driver for Oracle, version 2.573.2927.00
         or above, as detailed in the Description section of this article.
         
         


 
Description

Description:
     This sample will not work with the Oracle ODBC Driver, since the syntax used
     is specific to Microsoft's ODBC driver for Oracle.  See <BUG:866921> for
     further information.

Prerequisites:
     *  Microsoft ODBC Driver for Oracle, version 2.573.2927.00 or above
     *  Oracle RDBMS version 8.0.5 or above
     *  Oracle client software version 8.0.5 or above
     *  Visual Basic 6.0

Sample Output:
     A message box is output that contains the emp ID, ename, job.

 
References

<BUG:866921> ODBC Fails to Return Result From a Stored Procedure That Has Cursors

Information in this article was taken from Oracle Sample Code Repository Entry #782.

 
Sample Code

Option Explicit
' Global variables to set up connection
Dim Conn As ADODB.Connection
Dim AllCmd As ADODB.Command
Dim OneCmd As ADODB.Command
Dim RecSet As ADODB.Recordset

Private Sub Command1_Click()

End Sub

Private Sub Form_Load()
   Dim ConnStr As String
   Dim SQLStr As String
   
   ConnStr = "UID=scott;PWD=tiger;DSN=dsn817;"
   
   ' Make connection
   Set Conn = New ADODB.Connection
   With Conn
      .ConnectionString = ConnStr
      .CursorLocation = adUseClient
      .Open
   End With

   ' Get cursor from Stored proc containing all employees
   ' the number in resultset xx must be higher or equal to rows
   ' in cursor
   SQLStr = "{call retcursor.allemp({resultset 20, " _
        & "empno, ename, job})}"
   Set AllCmd = New ADODB.Command
   With AllCmd
      Set .ActiveConnection = Conn
      .CommandText = SQLStr
      .CommandType = adCmdText
   End With

   ' Sets up to get one emp from cursor with specific empno
   SQLStr = "{call retcursor.oneemp(?,{resultset 2, " _
        & " empno, ename, job})}"
   Set OneCmd = New ADODB.Command
   With OneCmd
      Set .ActiveConnection = Conn
      .CommandText = SQLStr
      .CommandType = adCmdText
      .Parameters.Append .CreateParameter(, adInteger, adParamInput)
   End With
   
   ' Set up recordset
   Set RecSet = New ADODB.Recordset
   With RecSet
      .CursorType = adOpenStatic
      .LockType = adLockReadOnly
   End With
End Sub

Private Sub cmdAll_Click()
   ' Display each row of record set in Message box
   Set RecSet.Source = AllCmd
   RecSet.Open
   While Not RecSet.EOF
      MsgBox "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & RecSet(2)
      RecSet.MoveNext
   Wend
   RecSet.Close
End Sub

Private Sub cmdOne_Click()
   ' Display specific employee in message box
   Dim inputssn As Long
   Set RecSet.Source = OneCmd
   inputssn = InputBox("Enter the Employee # you wish to retrieve:")
   OneCmd(0) = inputssn
   RecSet.Open
   MsgBox "Employee: " & RecSet(0) & ", " & RecSet(1) & ", " & RecSet(2)
   RecSet.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)
   ' Clean up
   Conn.Close
   Set Conn = Nothing
   Set AllCmd = Nothing
   Set OneCmd = Nothing
   Set RecSet = Nothing
End Sub

Private Sub cmdByeBye_Click()
    End
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com