Below is an example of an excell spreadsheet macro. This was programmed
using Visual Basic.
Attribute VB_Name = "CursorReturn"
Sub Cursor_Return()
Attribute Cursor_Return.VB_ProcData.VB_Invoke_Func = " \n14"
Const ORATYPE_NUMBER = 2
Const ORAPARM_INPUT = 1
Dim OraSession As Object
Dim OraDatabase As Object
Dim OraDynaset As Object
'Get deptno parameter from cell J2. If blank then use 10 as default
If Val(Worksheets("DataSheet").Cells(2, 10).Value) = 0 Then
dept = 10
Else
dept = Worksheets("DataSheet").Cells(2, 10).Value
End If
'Create the OraSession and connection objects
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'Create the Deptno parameter
OraDatabase.Parameters.Add "DEPTNO", dept, ORAPARM_INPUT
OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER
'Create OraDynaset based on "EmpCursor" created in stored procedure.
Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)
'Copy cursor to clipboard
OraDynaset.CopyToClipboard -1
'Erase range and paste clipboard
Sheets("DataSheet").Select
Range("A2:H50").Select
Selection.Clear
Range("A2").Select
ActiveSheet.Paste
'NOTE: To vary deptno values you do not need to run this entire procedure. Simply assign
' a new value to the parameter: OraDatabase.Parameters("DEPTNO").Value = 20 and issue
' a OraDynaset.Refresh command
End Sub