Oracle® Objects for OLE Developer's Guide 10g Release 1 (10.1) Part Number B10118-01 |
|
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