Skip Headers

Oracle® Objects for OLE Developer's Guide
10g Release 1 (10.1)

Part Number B10118-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Feedback

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