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)")