Get Array Macro found in Excell

Attribute VB_Name = "GetArray"
Sub Get_Array()
Attribute Get_Array.VB_ProcData.VB_Invoke_Func = " \n14"

Const ORAPARM_INPUT = 1
Const ORAPARM_OUTPUT = 2
Const ORATYPE_VARCHAR2 = 1
Const ORATYPE_NUMBER = 2

Dim OraSession As Object
Dim OraDatabase As Object
Dim OraDynaset As Object
Dim OraPLSQLStmt As Object
Dim OraPArray1 As Object
Dim OraPArray2 As Object

Dim PLSQLStmt1 As String
Dim PLSQLStmt2 As String
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("2:", "scott/tiger", 0&)

' PL/SQL procedure
PLSQLStmt1 = "CREATE OR REPLACE PACKAGE Employee AS " & _
 "TYPE numarray IS TABLE OF NUMBER INDEX by BINARY_INTEGER; " & _
 "TYPE vchar2array IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; " & _
 "PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, inEmpnos IN numarray, outEmpNames OUT vchar2array); " & _
 "PROCEDURE GetEmpName (inEmpno IN NUMBER, outEmpName OUT VARCHAR2); " & _
 "FUNCTION GetEmpSal (inEmpno IN NUMBER) RETURN NUMBER; " & _
 "END Employee;"

PLSQLStmt2 = "CREATE OR REPLACE PACKAGE BODY Employee AS " & _
  "PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY) IS " & _
  "BEGIN FOR i in 1..ArraySize loop SELECT ename INTO outEmpNames(i) FROM emp WHERE empno = inEmpNos(i); " & _
  "END LOOP; END; " & _
  "PROCEDURE GetEmpName (inEmpno IN NUMBER, outEmpName OUT VARCHAR2) IS BEGIN SELECT ename INTO outEmpName " & _
  "FROM EMP WHERE EMPNO = inEmpNo; END; FUNCTION GetEmpSal (inEmpno IN NUMBER) RETURN NUMBER IS " & _
  "outEmpsal NUMBER(7,2); BEGIN SELECT sal INTO outEmpsal FROM emp WHERE empno = inEmpno;  RETURN (outEmpsal); " & _
  "END; END Employee;"
' add the above procedure to the Server
OraDatabase.dbexecutesql (PLSQLStmt1)
OraDatabase.dbexecutesql (PLSQLStmt2)

' add the 2 parameters to the Server
OraDatabase.Parameters.addtable "empno", ORAPARM_INPUT, ORATYPE_NUMBER, 3, 22
OraDatabase.Parameters.addtable "empname", ORAPARM_OUTPUT, ORATYPE_VARCHAR2, 3, 10
Set OraPArray1 = OraDatabase.Parameters("empno")
Set OraPArray2 = OraDatabase.Parameters("empname")

' assign empno's to the array
OraPArray1.put_Value 7698, 0
OraPArray1.put_Value 7782, 1
OraPArray1.put_Value 7654, 2
 
' execute the procedure and retrieve the empname array
Set OraPLSQLStmt = OraDatabase.createsql("Begin Employee.GetEmpNamesInArray(3, :EMPNO, :EmpNAME); End;", &O0)

' display to screen
For j = 0 To 2
  parm_value = OraPArray2.Get_Value(j)
  msgbox parm_value
Next j

End Sub