Example: Inserting in a Collection

The following example illustrates how to insert elements in an Oracle collections. 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.

OraDynaset Example

Dim OraSession as OraSession

Dim OraDatabase as OraDatabase

Dim OraDynaset as OraDynaset

Dim EnameListNew as OraCollection

'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 new OraCollection object from the database

set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")

'set EnameListNew's element values

EnameListNew(1) = "Nasser"

EnameListNew(2) = "Chris"

EnameListNew(3) = "Gopal"

'create a dynaset object from department

set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

'start the AddNew operation and insert the EnameListNew collection

OraDynaset.AddNew

OraDynaset.Fields("dept_id") = 40

OraDynaset.Fields("name") = "DEVELOPMENT"

'set the EnameListNew to enames column

OraDynaset.Fields("enames") = EnameListNew

OraDynaset.Update

OraParameter Example

Dim OraSession as OraSession

Dim OraDatabase as OraDatabase

Dim EnameListNew as OraCollection

'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 new OraCollection object from the database

set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")

'set EnameListNew's element values

EnameListNew(1) = "Nasser"

EnameListNew(2) = "Chris"

EnameListNew(3) = "Gopal"

'create an input OraParameter object representing EnameList collection bind Variable

OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_INPUT, ORATYPE_VARRAY,"ENAMELIST"

'set the ENAMES parameter value to EnameListNew

OraDatabase.Parameters("ENAMES").Value = EnameListNew

'execute the insert sql statement

OraDatabase.ExecuteSQL ("insert into department values (40,'DEVELOPMENT', :ENAMES)")