How do I create an Oracle table with nested tables of object types?
22,170
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>
Comments
-
cagcowboy over 3 years
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.