I am trying to figure out how to properly use nested tables in Oracle. After I create type/table as below I can not insert an array of more then one value. I must be doing something wrong, but the insert below gives me a unique constraint PK_RESOURCEPROPERTIES_VALUE violation. PK_RESOURCEPROPERTIES_VALUE is a primary key on the nested table.
If I drop the primary key, then everything works. But in the case Oracle shows "full access table" on the nested table when performing a select. If I create an index on the nested table, than the plan is ok and the queries are fast, but I can not store a collection of more than one value.
If I make the nested table's primary key this way ( (constraint PK_RESOURCEPROPERTIES_VALUE PRIMARY KEY (NESTED_TABLE_ID, COLUMN_VALUE)) organization index compress ) then I can not have null values in this array.
create or replace type DM_tVarchar2Array as table of Varchar2(1024);
create table DM_RESOURCEPROPERTIES (
RESOURCEID INTEGER not null,
NAME VARCHAR2(128) not null,
constraint PK_RESOURCEPROPERTIES primary key (RESOURCEID, NAME)
) organization index
NESTED TABLE value STORE AS DM_RESOURCEPROPERTIES_VALUES
( (constraint PK_RESOURCEPROPERTIES_VALUE PRIMARY KEY (NESTED_TABLE_ID)) organization index overflow );
This one fails
insert into DM_RESOURCEPROPERTIES(RESOURCEID, NAME, VALUE)
values (1, 'name1', DM_tVarchar2Array('a1', 'a2'));