Go Premium for a chance to win a PS4. Enter to Win


Nested tables

Posted on 2006-11-23
Medium Priority
Last Modified: 2008-02-01
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);

    RESOURCEID   INTEGER not null,
    NAME         VARCHAR2(128) not null,
    VALUE        DM_tVarchar2Array,
  ) organization index
    ( (constraint PK_RESOURCEPROPERTIES_VALUE PRIMARY KEY (NESTED_TABLE_ID)) organization index overflow );
This one fails
  values (1, 'name1', DM_tVarchar2Array('a1', 'a2'));
Question by:imaniuk_atreus
  • 2
LVL 18

Expert Comment

ID: 18005104
LVL 27

Expert Comment

ID: 18007750
Conceptually what you are trying to do here is wrong.

NESTED_TABLE_ID is a unique value generated for each row in the table DM_RESOURCEPROPERTIES, and this ID is used to link to all the corresponding records in the "nested table". So, obviously there will be multiple records in the nested table for the same NESTED_TABLE_ID.
Creating a PK on this column doesnt make sense (is not possible also) in this scenario. Rather, if the nested table was on an object type, you could have used a combination of NESTED_TABLE_ID and a column from the object type to create the PK.

In short this insert "insert into DM_RESOURCEPROPERTIES(RESOURCEID, NAME, VALUE)
  values (1, 'name1', DM_tVarchar2Array('a1', 'a2'));"

Generates a NESTED_TABLE_ID corresponding to the record in the main table. And it inserts this ID into a hiddent column in the nested table along with values 'a1' and 'a2'. Since you have created a PK on NESTED_TABLE_ID it will fail.

Remove the PK constraint it will work.

Author Comment

ID: 18007798
You are right, it will work, but then a full nested table scan will be used to find the values for every parent record. I want to avoid that. That is why I need an index there. The only way I have found it will work is to create a pk on (NESTED_TABLE_ID, COLUMN_VALUE). Those are both hidden columns. This makes sure that the values in the array are unique (desirable), but a null/empty string is no longer allowed as an element of the nested array/collection. I can probably live with this limitation, but was wondering if there is a way to have an index and be able to store nulls in the array.
LVL 27

Accepted Solution

sujith80 earned 500 total points
ID: 18007853
>>"but then a full nested table scan will be used to find the values for every parent record"

Even if you have an index on this nested table how can this be avoided for every parent record?. This PK or index on the nested table could be helpful in case of nested tables of object types; and when you are manipulating individual nested table elements using the TABLE operator. At the main table level; the indexes may not be helpful.

Still if you like to have an index on the nested table; try out creating an index on the nested table itself; donno how far it will be useful:

something like:

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question