Example: Creating a Variant SAFEARRAY from a Collection
The following example illustrates how to get and set a Variant SAFEARRAY with
an Oracle collection. Before running the sample code, make sure that you have
the necessary datatypes and tables in the database. See Schema Description used in examples of OraCollection.
Creating Variant SAFEARRAY from collection
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim OraDynaset as OraDynaset
Dim EnameList as OraCollection
Dim EnameArray as Variant
'create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
'create a dynaset object from department
set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)
'retrieve a Enames column from Department.
'Here Value property of OraField objectreturns EnameList OraCollection
set EnameList = OraDynaset.Fields("Enames").Value
'get the Variant SAFEARRAY from the collection.
EnameArray = EnameList.SafeArray
'display the individual elements of EnameArray
msgbox EnameArray(0)
msgbox EnameArray(1)
msgbox EnameArray(2)
Setting Variant SAFEARRAY to the collection
Dim OraSession as OraSession
Dim OraDatabase as OraDatabase
Dim EnameList as OraCollection
Dim EnameArray() As String
ReDim EnameArray(3)
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger",
0&)
'create an Empty uninitialized input OraParameter object
'represent EnameList collection bind Variable
OraDatabase.Parameters.Add "ENAMES", Empty, ORAPARM_INPUT,
ORATYPE_VARRAY,"ENAMELIST"
'get the Empty uninitialized ENAMES parameter value
set EnameList = OraDatabase.Parameters("ENAMES").Value
'initialize the EnameArray
EnameArray(0) = "Nasser"
EnameArray(1) = "Chris"
EnameArray(2) = "Gopal"
'set the EnameArray to EnameList's SafeArray
EnameList.SafeArray = EnameArray
'execute the insert sql statement
OraDatabase.ExecuteSQL ("insert into department
values (40,'DEVELOPMENT', :ENAMES)")