Result Sets Work With Procedures But Not Functions?

From: <dressen_at_my-deja.com>
Date: Fri, 21 Jan 2000 03:10:29 GMT
Message-ID: <868imq$5qq$1@nnrp1.deja.com>

I'm having trouble getting Result Sets returned back from Oracle with Functions. Procedures are working fine though. Has anyone experienced this, and know any additional information? Is there something I'm missing here?

I am currently using Oracle 8.0.5, Oracle ODBC 8.0.5.7, and MS VB 5.0 SP3.

I've adopted some of Mark Tomlinson's code from Thomas Kyte's web page about result sets (http://osi.oracle.com/~tkyte/ResultSets/index.html) to show the problem.

If anyone has any ideas, it would be much appreciated.

-Mark

' 1) Create a form with 1 Text control (Text1) and 1 List Control
(List1) and
' 2 Buttons (btnProcedure, btnFunction).
' 2) Paste in the following code and change the Connection String.
'
'
'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;
' Function FuncEmpData
'(en in varchar2,EmpCursor in out empCur)
' return integer
' is
'begin
' open EmpCursor for select ename from emp where ename LIKE en;
' return 1;
'end;
'end;

Dim cn As New rdoConnection

     Dim qd As rdoQuery
     Dim rs As rdoResultset
     Dim cl As rdoColumn

Private Sub btnFunction_Click()
     sSQL = "{? = call RefTest.FuncEmpData(?)}"

     Set qd = cn.CreateQuery("", sSQL)

     qd.rdoParameters(0).Type = rdTypeINTEGER
     qd(0).Direction = rdParamReturnValue

     qd.rdoParameters(1).Type = rdTypeVARCHAR
     qd(1).Direction = rdParamInputOutput
     qd(1).Value = Text1.Text
     'qd.rdoParameters(1).Type = rdTypeVARCHAR

     'Dynamic or Keyset is meaningless here
     Set rs = qd.OpenResultset(rdOpenStatic)
     List1.AddItem ("Function Result = " & qd(0).Value)

     Call Print_It(rs)

End Sub

Private Sub btnProcedure_Click()

     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)
     Call Print_It(rs)

End Sub

Private Sub Print_It(rs As rdoResultset)

    Do

        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
fornull fields
                Else
                    List1.AddItem cl.Value
                    ' Debug.Print " "; cl.Name; " "; cl.Value;
                End If
            Next
            Debug.Print
            rs.MoveNext
        Loop
     Loop While rs.MoreResults

End Sub

Private Sub Form_Load()

     cn.Connect = "uid=scott; pwd=tiger;DSN=el_Oracle;"
     'enable the MS Cursor library
     cn.CursorDriver = rdUseOdbc
     'Make the connection
     cn.EstablishConnection rdNoDriverPrompt

End Sub

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 21:10:29 CST