How do I create an Oracle table with nested tables of object types?

Question

I have a highly nested OBJECT TYPE structure that allows me to hold data in a tree-like manner.

I'd now like to create a table to cache these objects, but I'm having difficulty getting the table to create. I suspect my problem is the multiple nesting levels, but not sure how to fix.

CREATE TYPE STEP_OBJ AS OBJECT (
  ID                        VARCHAR2(20),
  OTHER_FIELDS              VARCHAR2(100));
/

CREATE TYPE STEP_TAB_OBJ AS TABLE OF STEP_OBJ;
/

CREATE TYPE TREE_OBJ AS OBJECT (
  STEPS       STEP_TAB_OBJ,
  COMPLETE    VARCHAR2(1));
/

CREATE TYPE TABLE_OF_TREE_OBJ AS TABLE OF TREE_OBJ;
/

CREATE TABLE TREE_TABLE_CACHE (
   HANDLE               VARCHAR2(20),
   TABLE_OF_TREES       TABLE_OF_TREE_OBJ)
   NESTED TABLE TABLE_OF_TREES STORE AS TREE_TABLE_CACHE_OBJS;
/

CREATE TABLE TREE_TABLE_CACHE (
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column TABLE_OF_TREES
ORA-22913: must specify table name for nested table column or attribute

Oracle 11g.

APC · Accepted Answer · 2012-02-26 18:10:14Z

6

You need to specify a storage clause for the inner collection. The syntax is simply to wrap the clause for the nested collection in brackets like this:

SQL> CREATE TABLE TREE_TABLE_CACHE (
  2     HANDLE               VARCHAR2(20),
  3     TABLE_OF_TREES       TABLE_OF_TREE_OBJ)
  4     NESTED TABLE TABLE_OF_TREES STORE AS TREE_TABLE_CACHE_OBJS
  5     ( NESTED TABLE STEPS          STORE AS TREE_TABLE_CACHE_STEPS)
  8   /

Table created.

SQL>

answered Feb 26, 2012 at 18:10

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I create an Oracle table with nested tables of object types?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related