Creating a VARRAY Collection Type

The example code below creates a VARRAY collection type ENAMELIST and department table having ENAMELIST collection type column.

set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

set hrDb = OO4OSession.OpenDatabase("Exampledb",

"scott/tiger", 0)

hrDb.ExecuteSQL("CREATE TYPE ENAMELIST AS VARRAY(20) OF

VARCHAR2(30)")

hrDb.ExecuteSQL("CREATE TABLE department (dept_id NUMBER(2),name

VARCHAR2(15),ENAMES ENAMELIST)")

The following script inserts some collection data into department table:

hrDb.ExecuteSQL("INSERT INTO department VALUES(10, 'ACCOUNTING',

ENAMELIST('KING','CLARK','MILLER') )")

hrDb.ExecuteSQL("INSERT INTO department VALUES(20, 'RESEARCH',

ENAMELIST('JONES','SCOTT','ADAMS','SMITH','FORD') )")

hrDb.ExecuteSQL("INSERT INTO department VALUES(30, 'SALES',

ENAMELIST('BLAKE','MARTIN','ALLEN','TURNER','JAMES') )")