Re: Oracle and ODBC: returning cursor results from stored function

Here is a working example, but it requires the use of the 8.0.5.2.0 or later Oracle ODBC driver, and an 8.0.5 server.

'
' 1) Create a form with 1 Text control (Text1) and 1 List Control
(List1) and
' 1 Button (btnExecute).
' 2) The only code that you need is a Click method on your button.
Here is the Code.
'
'

Private Sub btnExecute_Click()
'PL/SQL Code
'===========
'
'CREATE OR REPLACE package reftest as
' cursor c1 is select ename from emp;
' type empCur is ref cursor return c1%ROWTYPE;
' Procedure GetEmpData(en in varchar2,EmpCursor in out empCur);
'END;
'
'
'CREATE OR REPLACE package body reftest as
' Procedure GetEmpData
'(en in varchar2,EmpCursor in out empCur) is
'begin
' open EmpCursor for select ename from emp where ename LIKE en;
'end;
'end;
'

     Dim cn As New rdoConnection
     Dim qd As rdoQuery
     Dim rs As rdoResultset
     Dim cl As rdoColumn
     Static Number As Integer
     
     List1.Clear
     Number = 0
     cn.Connect = "uid=scott; pwd=tiger; DSN=MSLANGORL;"
     'enable the MS Cursor library
     cn.CursorDriver = rdUseOdbc
     'Make the connection
     cn.EstablishConnection rdNoDriverPrompt
      
     sSQL = "{call RefTest.GetEmpData(?,?)}"
     
     Set qd = cn.CreateQuery("", sSQL)
          
     qd.rdoParameters(0).Type = rdTypeVARCHAR
     qd(0).Direction = rdParamInputOutput
     qd(0).Value = Text1.Text
     qd.rdoParameters(1).Type = rdTypeVARCHAR

     'Dynamic or Keyset is meaningless here
     Set rs = qd.OpenResultset(rdOpenStatic)

     Do
        Debug.Print
        Debug.Print
        
        Do Until rs.EOF
            For Each cl In rs.rdoColumns
                 If IsNull(cl.Value) Then
                    List1.AddItem "(null)"
                    ' Debug.Print " "; cl.Name; "NULL"; Error trap for
null fields
                Else
                    List1.AddItem cl.Value
                    ' Debug.Print " "; cl.Name; " "; cl.Value;
                End If
            Next
            Debug.Print
            rs.MoveNext
        Loop
     Loop While rs.MoreResults
     cn.Close

End Sub Received on Thu Jan 28 1999 - 19:09:38 CST