Coding Example - Multiple Cursor Return

This example demonstrates the use of OraParameter object supporting PL/SQL cursor This example returns PL/SQL cursor as a dynaset for the different values of Customer name parameter. Make sure that 'Customers' stored procedure (found in multicur.sql) is available in the Oracle Server and paste this code into the definition section of a form, then press F5.

Private Sub Example_Click()

'Declare variables as OLE Objects.

Dim MySession as OraSession

Dim MyDatabase as OraDatabase

Dim OrderDynaset As OraDynaset

Dim SalesDynaset As OraDynaset

'Create the OraSession Object.

Set MySession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object by opening a connection to Oracle.

Set MyDatabase = MySession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

'Create the Deptno parameter

MyDatabase.Parameters.Add "NAME", "JOCKSPORTS", ORAPARM_INPUT

MyDatabase.Parameters("NAME").ServerType = ORATYPE_VARCHAR2

MyDatabase.Parameters.Add "ORDCURSOR", 0, ORAPARM_OUTPUT

MyDatabase.Parameters("ORDCURSOR").ServerType = ORATYPE_CURSOR

MyDatabase.Parameters.Add "SALESCURSOR", 0, ORAPARM_OUTPUT

MyDatabase.Parameters("SALESCURSOR").ServerType = ORATYPE_CURSOR

Set OraSQLStmt = MyDatabase.CreateSql("Begin Customers.GetCutomerSalesOrder(:Name,:OrdCursor,:SalesCursor);end;" ,ORASQL_FAILEXEC)

Set OrderDynaset = MyDatabase.Parameters("ORDCURSOR").Value

Set SalesDynaset = MyDatabase.Parameters("SALESCURSOR").Value

'Now display the Dynaset's field value

MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " & OrderDynaset.fields("orderDate").Value

MsgBox "Sales Details are " & SalesDynaset.fields("Prodid").Value & " " & SalesDynaset.fields("ProdName").Value

'Now Change the customer name to VOLLYRITE

MyDatabase.Parameters("NAME").Value = "VOLLYRITE"

'Now refreshes the SQLStmt object

OraSQLStmt.Refresh

'Now display the Dynaset's field value

MsgBox "Order Details are " & OrderDynaset.fields("ordid").Value & " " & OrderDynaset.fields("orderDate").Value

MsgBox "Sales Details are " & SalesDynaset.fields("Prodid").Value & " " & SalesDynaset.fields("ProdName").Value

'Now remove the Parameters object

'MUST BE CALLED for OraParameter of type ORATYPE_CURSOR

MyDatabase.Parameters.Remove ("ORDCURSOR")

MyDatabase.Parameters.Remove ("SALESCURSOR")

MyDatabase.Parameters.Remove ("NAME")

End Sub