Object Tables   «Prev 

Nested Tables in Oracle

CREATE OR REPLACE TYPE PETSTORE.SALE_DETAIL
AS OBJECT
 (PRODUCT_ID REF PETSTORE.PRODUCT_TYPE,
  ITEM QTY NUMBER(3,0)
  DETAIL_AMOUNT NUMBER(10,2)
);
First, create an object SALE_DETAIL with its attributes as defined

CREATE OR REPLACE TYPE PETSTORE.DETAIL_TABLE
AS TABLE OF SALE_DETAIL;
After creating the object, create a nested table using the following DDL statement:

CREATE TABLE PETSTORE.SALE_HEADER
(SALE_ID NUMBER(10,0),
  CUST_REF REF PETSTORE.CUSTOMER_TYPE,
DETAIL_TOTAL NUMBER(10,2),
TAX_AMOUNT NUMBER(10,2),
SHIPPING_AMOUNT NUMBER(10,2),
SALE_TOTAL NUMBER(10,2),
RETAIL_NEST DETAIL_TABLE
)
After creating the nested table, create a different table and associate the nested table with it, as shown within the DDL statement.