CREATE TABLE list_master
( list_id           NUMBER       NOT NULL
 ,list_description  VARCHAR2(30) NOT NULL
 ,list_notes        VARCHAR2(500)
 ,CONSTRAINT list_master_pk PRIMARY KEY (list_id)
);


CREATE TABLE list_items
( list_item_id      NUMBER       NOT NULL
 ,list_id           NUMBER       NOT NULL
 ,list_item         VARCHAR2(20) NOT NULL
 ,CONSTRAINT  list_items_pk PRIMARY KEY (list_item_id)
 ,CONSTRAINT  list_items_list_master_fk
  FOREIGN KEY (list_id)
  REFERENCES  list_master(list_id)
);

CREATE INDEX list_items_ak
  ON list_items (list_id, list_item);


-- Sample Data
					
-- Master List
INSERT INTO list_master (list_id,list_description,list_notes) VALUES (10,'TEST NUMBER',NULL);
INSERT INTO list_master (list_id,list_description,list_notes) VALUES (20,'TEST CHARACTER',NULL);
INSERT INTO list_master (list_id,list_description,list_notes) VALUES (30,'TEST DATE',NULL);
COMMIT;

-- List Items
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (1,10,'20');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (2,10,'30');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (3,20,'FORD');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (4,20,'SMITH');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (5,30,'4/2/1981');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (6,30,'11/17/1981');
COMMIT;

SQL>-- Setting Date Format for Session (Connect as Scott).
SQL>ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';

Session altered.

SQL>
SQL>-- Number List Example
SQL>SELECT empno, deptno, ename, hiredate FROM emp
  2    WHERE deptno IN (SELECT TO_NUMBER(list_item) FROM list_items WHERE list_id = 10);

     EMPNO     DEPTNO ENAME      HIREDATE
---------- ---------- ---------- ----------
      7369         20 SMITH      12/17/1980
      7499         30 ALLEN      02/20/1981
      7521         30 WARD       02/22/1981
      7566         20 JONES      04/02/1981
      7654         30 MARTIN     09/28/1981
      7698         30 BLAKE      05/01/1981
      7788         20 SCOTT      04/19/1987
      7844         30 TURNER     09/08/1981
      7876         20 ADAMS      05/23/1987
      7900         30 JAMES      12/03/1981
      7902         20 FORD       12/03/1981

11 rows selected.

SQL>
SQL>-- Character List Example
SQL>SELECT empno, deptno, ename, hiredate FROM emp
  2    WHERE ename IN (SELECT list_item FROM list_items WHERE list_id = 20);

     EMPNO     DEPTNO ENAME      HIREDATE
---------- ---------- ---------- ----------
      7369         20 SMITH      12/17/1980
      7902         20 FORD       12/03/1981

SQL>
SQL>-- Date List Example
SQL>SELECT empno, deptno, ename, hiredate FROM emp
  2    WHERE hiredate IN (SELECT TO_DATE(list_item, 'mm/dd/yyyy') FROM list_items WHERE list_id = 30);

     EMPNO     DEPTNO ENAME      HIREDATE
---------- ---------- ---------- ----------
      7566         20 JONES      04/02/1981
      7839         10 KING       11/17/1981