Re: PL/SQL, VB5, ODBC & Resultsets

From: Mark Tomlinson <marktoml_at_gdi.net>
Date: Mon, 14 Sep 1998 19:35:00 GMT
Message-ID: <36056f84.85377656@newshost.us.oracle.com>

here is an example:

'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(EmpCursor in out empCur );
'END;
'
'create or replace package body reftest as
' procedure GetEmpData(EmpCursor in out empCur) is
'begin
' open EmpCursor for select ename from emp;
'end;
'end;
'

'VB Code

Private Sub Command2_Click()

     Dim cn As New rdoConnection
     Dim qd As rdoQuery
     Dim rs As rdoResultset
     Dim cl As rdoColumn
     Static Number As Integer
     
     Number = 0
     cn.Connect = "uid=scott; pwd=tiger; DSN=Oracle;"
     '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

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

     'all the output is routed to the debug window
     Debug.Print ".....Starting Result Sets....."
    
     Do
        Debug.Print
        Debug.Print
        
        Do Until rs.EOF
            For Each cl In rs.rdoColumns
                 If IsNull(cl.Value) Then
                    Debug.Print " "; cl.Name; "NULL"; 'Error trap for
null fields
                Else
                    Debug.Print " "; cl.Name; " "; cl.Value;
                End If
            Next
            Debug.Print
            rs.MoveNext
        Loop
     Loop While rs.MoreResults
     cn.Close

End Sub Received on Mon Sep 14 1998 - 14:35:00 CDT