CREATE OR REPLACE TYPE tp_sub_data_countries_tb IS TABLE OF VARCHAR2(3);

CREATE OR REPLACE TYPE tp_sub_data_countries_tb IS TABLE OF VARCHAR2(3); 

CREATE OR REPLACE TYPE tp_sub_data_categories_tb IS TABLE OF VARCHAR2(30); 

CREATE OR REPLACE TYPE tp_conditions IS OBJECT 
( 
    sub_countries      tp_sub_data_countries_tb 
    , sub_categories   tp_sub_data_categories_tb  
); 

CREATE OR REPLACE TYPE tp_conditions_tab IS TABLE OF tp_conditions; 

CREATE OR REPLACE TYPE tp_sub_data IS OBJECT 
( 
      object_name           VARCHAR2(100)       --Ex: "a fancy name" 
    , object_event_type     VARCHAR2(10)        --Ex: create 
    , object_type           VARCHAR2(20)        --Ex: `product` considered for now 
    , object_event_match    VARCHAR2(10)        --Ex 'exact' match considered for now 
    , conditions            tp_conditions_tab   --Ex "sub_countries": ["IT", "DE"], "sub_categories": ["HI-FI", "computers"] 
); 

CREATE OR REPLACE TYPE tp_sub_data_tab IS TABLE OF tp_sub_data; 

CREATE SEQUENCE seq_subscriptions START WITH 1 MINVALUE 1 INCREMENT BY 1 NOCYCLE NOCACHE NOORDER

CREATE TABLE subscriptions 
( 
    ID                  NUMBER (*, 0) 
    , SUBSCRIBER        NUMBER (*, 0)       NOT NULL ENABLE 
    , DATA_IN           DATE                NOT NULL ENABLE 
    , DATA_OUT          DATE 
    , SUB_DATA          tp_sub_data_tab   
    , CREATED_BY        NUMBER (*, 0)	    NOT NULL ENABLE 
    , CREATION_DATE     DATE                DEFAULT SYSDATE         NOT NULL ENABLE 
    , LAST_UPDATED_BY   NUMBER (*, 0)	    NOT NULL ENABLE 
    , LAST_UPDATE_DATE  DATE                DEFAULT SYSDATE         NOT NULL ENABLE 
    , TIME_STAMP        TIMESTAMP(3)        DEFAULT SYSTIMESTAMP    NOT NULL ENABLE 
    , CONSTRAINT subscriptions_pk PRIMARY KEY (ID) USING INDEX (CREATE UNIQUE INDEX subscriptions_UIX01 ON subscriptions(ID)) 
) 
NESTED TABLE SUB_DATA store as subscriptions_data_tab (NESTED TABLE conditions STORE AS conditions_data_tab(NESTED TABLE sub_countries STORE AS countries_tab, NESTED TABLE sub_categories STORE AS categories_tab)) 
return as locator

insert into subscriptions VALUES 
( 
1, 12345, trunc(sysdate), NULL 
, tp_sub_data_tab 
    (tp_sub_data 
        ('a fancy name', 'create', 'product', 'exact' 
        , tp_conditions_tab 
            ( 
                tp_conditions( 
                    tp_sub_data_countries_tb('IT', 'DE'), tp_sub_data_categories_tb('HI-FI', 'computers') 
                    ) 
            ) 
    ) 
    ) 
, 147478 
, sysdate 
, 147478 
, sysdate 
, systimestamp  
)

select * from subscriptions s 
JOIN TABLE (s.sub_data) s1 ON 1=1 
JOIN TABLE (s1.conditions) s2 on 1=1 
JOIN TABLE (s2.sub_countries) s3 on 1=1 
JOIN TABLE (s2.sub_categories) s4 on 1=1