CREATE TABLE TOM_RESER_DERESERVATION
(
  ELEMENT_id           VARCHAR2(200 BYTE),
  ELEMENT_LABEL        VARCHAR2(200 BYTE),
  PRODUCT_NAME         VARCHAR2(100 BYTE),
  CIRCLE               VARCHAR2(100 BYTE),
  COUNTRY              VARCHAR2(150 BYTE)
) partition     by list (COUNTRY) 
  subpartition  by list (circle)
(
partition P1 values('INDIA')
(
 subpartition EP11 values('ANE'),
 subpartition EP12 values('AP'),
 subpartition EP13 values('BIH'),
 subpartition EP14 values('CHN'),
 subpartition EP15 values('DEL'),
 subpartition EP16 values('GUJ'),
 subpartition EP17 values('HAR'),
 subpartition EP18 values('HP'),
 subpartition EP19 values('JNK'),
 subpartition EP20 values('KAR'),
 subpartition EP21 values('KER'),
 subpartition EP22 values('KOL'),
 subpartition EP23 values('MAH'),
 subpartition EP24 values('MP'),
 subpartition EP25 values('MUM'),
 subpartition EP26 values('NLD'),
 subpartition EP27 values('ORS'),
 subpartition EP28 values('PUN'),
 subpartition EP29 values('RAJ'),
 subpartition EP30 values('ROB'),
 subpartition EP31 values('ROTN'),
 subpartition EP32 values('UPE'),
 subpartition EP33 values('UPW'),
 subpartition EP63 values(default)
)
) enable row movement

begin
insert into TOM_RESER_DERESERVATION values('1','ABC','Light','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Wires','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Nuts','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('1','ABC','Light','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Wires','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Nuts','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('4','XYZ','Clips','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('1','ABC','Rubbers','MUM','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Typres','MUM','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Chains','MUM','INDIA');
end;


CREATE TABLE TOM_RESER_PARTITION_LIST
(
  PARTITION       VARCHAR2(50 BYTE),
  PARTITION_NAME  VARCHAR2(50 BYTE),
  COUNTRY          VARCHAR2(100 BYTE)
);

select * from TOM_RESER_PARTITION_LIST;

create type circle as table of varchar2(25);

CREATE TABLE USER_TAB
(
  SR_NO         NUMBER                          NOT NULL,
  USER_MAIL_ID  VARCHAR2(280 BYTE),
  PASSWORD      VARCHAR2(300 BYTE)              NOT NULL,
  USER_TYPE     VARCHAR2(60 BYTE)               NOT NULL,
  CIRCLES       CIRCLE
) nested table circles store as Nest_TOM;

create table user_partition_mapping ( 
    USER_SR_NO      NUMBER,
    PARTITION_NAME  VARCHAR2(50 BYTE)
);

CREATE OR REPLACE TRIGGER user_mapping
   AFTER INSERT
   ON user_tab
   FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   sqlcodes   VARCHAR2 (100);
   sqlmsg     VARCHAR2 (400);
BEGIN
   INSERT INTO user_partition_mapping
      (SELECT *
         FROM (WITH w AS
                    (SELECT :NEW.sr_no sr_no, t.COLUMN_VALUE circle
                       FROM DUAL, TABLE (:NEW.circles) t)
               SELECT w.sr_no, PARTITION
                 FROM tom_reser_partition_list, w
                WHERE partition_name = w.circle));

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      sqlcodes := SQLCODE;
      sqlmsg := SQLERRM;

      INSERT INTO user_creation_errors
           VALUES (sqlcodes, sqlmsg);

      COMMIT;
END reser_user_mapping;


insert into USER_TAB values(1,'ABC@Ora.com','QWERT','Circle',circle('ANE','HP'));

insert into USER_TAB values(2,'ABC@Ora.com','QWERT','Circle',circle('MUM'));

WITH w AS
     (SELECT urm.partition_name, u.sr_no, u.user_type
        FROM user_partition_mapping urm, user_tab u
       WHERE u.sr_no = urm.user_sr_no
         AND u.user_mail_id = 'ABC@Ora.com')
SELECT ELEMENT_ID, ELEMENT_LABEL, PRODUCT_NAME, CIRCLE, COUNTRY
  FROM TOM_RESER_DERESERVATION SUBPARTITION (select distinct partition_name from w);