Retrieve multiple Oracle Refcursors in MS Access 1

[TyzA]

Hello,

I have this MS access application which needs to retrieve multiple refcursors from an Oracle database and export this to a MS Excel workbook. Per refcursor a sheet needs to be used to place the contents of the refcursor.

What I have so far is that I can retrieve the first refcursor returned by the Oracle PL/SQL but I can't get to the others.

Is it possible with VBA (in MS Access) to get to the others as well? How?

Here is my PL/SQL code. It is returning 3 refcursors

PROCEDURE testproc3(Paramlist OUT retRefCur, Paramlist2 OUT retRefCur2, Paramlist3 OUT retRefCur3, compId IN NUMBER) IS
   
  BEGIN
    
    
    OPEN Paramlist
      FOR SELECT base_component_id, usage_group_id, terminating_zone, upper_limit, tier1,tier2, tier3,tier4,recurring_charge
           FROM ot_component_rating
           where base_component_id = compId
           ORDER BY base_component_id, usage_group_id, terminating_zone, upper_limit, tier1,tier2, tier3,tier4,recurring_charge;
           
    OPEN Paramlist2
      FOR SELECT base_component_id, usage_group_id, terminating_zone, upper_limit, tier1,tier2, tier3,tier4,recurring_charge
           FROM ot_component_rating
           where base_component_id = (compId+3)
           ORDER BY base_component_id, usage_group_id, terminating_zone, upper_limit, tier1,tier2, tier3,tier4,recurring_charge;
           
           
     OPEN Paramlist3
      FOR SELECT base_component_id, usage_group_id, terminating_zone, upper_limit, tier1,tier2, tier3,tier4,recurring_charge
           FROM ot_component_rating
           where base_component_id = (compId+100)
           ORDER BY base_component_id, usage_group_id, terminating_zone, upper_limit, tier1,tier2, tier3,tier4,recurring_charge;
        
  END;

Here is my MS Access code so far.

Private Sub Command2_Click()
Dim Conn As ADODB.Connection
Dim sProcCmd As ADODB.Command
Dim sConn As String 'Conncetion String
Dim sCmdText As String 'Command Text
Dim sSQL As String 'SQL Output
Dim ret As Integer

Set rs
tIn = New ADODB.Recordset
Set rstOut = New ADODB.Recordset

'get the value from the form
Component_Id = Forms![callhistory].[compid]

'set the ODBC connection
sConn = "Driver={Oracle in XE};Server=XE;Uid=kafka;Pwd=kafka" 'very important to use correct driver!!!!!

'Set the proc to execute
sCmdText = "KAFKA.KAFKATEST.testproc3"

Set Conn = New ADODB.Connection
Conn.ConnectionString = sConn
Conn.CursorLocation = adUseServer
Conn.Open

Set sProcCmd = New ADODB.Command
Set sProcCmd.ActiveConnection = Conn
sProcCmd.CommandType = adCmdStoredProc

sProcCmd.CommandText = sCmdText
sProcCmd.Parameters.Append sProcCmd.CreateParameter("compId", adInteger, adParamInput, , Component_Id) 'set input parameter

On Error Resume Next

Set rstOut = sProcCmd.Execute

If Conn.Errors.Count > 0 Then
   For Each e In Conn.Errors
       MsgBox e.Description
    Next

Else
 
 ' Check to see if ref cursor was opened?
  If (rstOut.State <> adStateClosed) Then

' Create an instance of Excel and add a workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
  
    ' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True
    
    ' Copy field names to the first row of the worksheet
    fldCount = rstOut.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rstOut.Fields(iCol - 1).Name
    Next
    
    ' Copy the recordset to the worksheet, starting in cell A2
    xlWs.Cells(2, 1).CopyFromRecordset rstOut
        
    ' Auto-fit the column widths and row heights
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit

    ' Close ADO objects
    rstOut.Close
    Set rstOut = Nothing
    
    ' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing

    Set xlApp = Nothing
 
 Else
    MsgBox "Recordset was not opened since ref cursor was not opened " & _
           "in the stored procedure." & vbCrLf & vbCrLf & "State: " & rstOut.State
  End If
 
End If
 
CallSProc3 = True


Set Conn = Nothing
End Sub

Any help is much appreciated.
-T

 

[misterstick]

PROCEDURE testproc3(Paramlist OUT retRefCur, Paramlist2 OUT retRefCur2, Paramlist3 OUT retRefCur3, compId IN NUMBER) IS

sProcCmd.CommandText = sCmdText
sProcCmd.Parameters.Append sProcCmd.CreateParameter("compId", adInteger, adParamInput, , Component_Id) 'set input parameter
' need three more parameters defined as adParamOutput...
Set rstOut = sProcCmd.Execute

mr s. <

 

[TyzA]

And which datatype should these parameters have? In Oracle they are of the type Refcursor

I've tried several but I always get an error that there is a mismatch in datatype

-T

 

[misterstick]

http://www.velocityreviews.com/forums/showpost.php?p=131271&postcount=6

should get you thinking...

mr s. <

 

[TyzA]

Thanks but I already tried this. It works for .NET but not for VBA. Is it actually possible to retrieve multiple Oracle ref cursors with VBA?

-T

 

[JerryKlmns]

Would NextRecordset method be of help? Or is there a Union type query in Oracle, so you could create a dummy field for the recordset to define the base_component_id (0, +3, +100)?

 

[TyzA]

The NextRecordset did it.
Thanks all for your help.

-T