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