create table TABLE1 (id varchar2(5),Dept_Inclusion varchar2(100),Dept_Exclusion varchar2(100));
 
insert into TABLE1 VALUES('E1','3 AND 4 AND 5 AND 6 AND 7','1 OR 2');
insert into TABLE1 VALUES('E2','(1 OR 2) AND 3','4 OR 5');
insert into TABLE1 VALUES('E3','1 AND 2 AND (3 OR 4) AND 5','6 OR 7');
insert into TABLE1 VALUES('E1','1 AND 3 AND 5','2 OR (3 AND 4)');
 
create table TABLE2 (id varchar2(5),Dept number);

insert into TABLE2 VALUES('E1',3);
insert into TABLE2 VALUES('E1',4);
insert into TABLE2 VALUES('E1',5);
insert into TABLE2 VALUES('E1',6);
insert into TABLE2 VALUES('E1',7);
insert into TABLE2 VALUES('E2',1);
insert into TABLE2 VALUES('E2',3);
insert into TABLE2 VALUES('E2',4);
insert into TABLE2 VALUES('E3',1);
insert into TABLE2 VALUES('E3',2);
insert into TABLE2 VALUES('E3',4);
insert into TABLE2 VALUES('E3',5);
insert into TABLE2 VALUES('E4',1);
insert into TABLE2 VALUES('E4',3);
insert into TABLE2 VALUES('E4',5);
insert into TABLE2 VALUES('E4',4);

SCOTT@orcl12c> COLUMN dept_inclusion FORMAT A30
SCOTT@orcl12c> COLUMN dept_exclusion FORMAT A30
SCOTT@orcl12c> SELECT * FROM table1
  2  /

ID    DEPT_INCLUSION                 DEPT_EXCLUSION
----- ------------------------------ ------------------------------
E1    3 AND 4 AND 5 AND 6 AND 7      1 OR 2
E2    (1 OR 2) AND 3                 4 OR 5
E3    1 AND 2 AND (3 OR 4) AND 5     6 OR 7
E4    1 AND 3 AND 5                  2 OR (3 AND 4)

4 rows selected.

SCOTT@orcl12c> SELECT * FROM table2
  2  /

ID          DEPT
----- ----------
E1             3
E1             4
E1             5
E1             6
E1             7
E2             1
E2             3
E2             4
E3             1
E3             2
E3             4
E3             5
E4             1
E4             3
E4             5
E4             4

16 rows selected.

SCOTT@orcl12c> CREATE MATERIALIZED VIEW LOG ON table2
  2  WITH ROWID, SEQUENCE (id, dept)
  3  INCLUDING NEW VALUES
  4  /

Materialized view log created.

SCOTT@orcl12c> CREATE MATERIALIZED VIEW tab2_mview
  2  REFRESH FAST ON COMMIT
  3  AS
  4  SELECT id,
  5  	    LISTAGG (dept, ' ') WITHIN GROUP (ORDER BY dept) AS depts
  6  FROM   table2
  7  GROUP  BY id
  8  /

Materialized view created.

SCOTT@orcl12c> COLUMN depts FORMAT A30
SCOTT@orcl12c> SELECT * FROM tab2_mview
  2  /

ID    DEPTS
----- ------------------------------
E1    3 4 5 6 7
E2    1 3 4
E3    1 2 4 5
E4    1 3 4 5

4 rows selected.

SCOTT@orcl12c> CREATE INDEX tab2_view_idx ON tab2_mview (depts)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('SYNC (ON COMMIT)')
  4  /

Index created.

SCOTT@orcl12c> SELECT t1.id
  2  FROM   table1 t1, tab2_mview t2
  3  WHERE  t1.id = t2.id
  4  AND    CONTAINS
  5  	      (t2.depts,
  6  	       t1.dept_inclusion) > 0
  7  AND    NOT CONTAINS
  8  	      (t2.depts,
  9  	       t1.dept_exclusion) > 0
 10  /

ID
-----
E1
E3

2 rows selected.

SCOTT@orcl12c> CREATE OR REPLACE TYPE id_typ AS TABLE OF VARCHAR2(5);
  2  /

Type created.

SCOTT@orcl12c> VARIABLE g_ref REFCURSOR
SCOTT@orcl12c> DECLARE
  2    v_id   VARCHAR2(5);
  3    v_ids  id_typ := id_typ();
  4  BEGIN
  5    FOR r1 IN (SELECT * FROM table1) LOOP
  6  	 BEGIN
  7  	   SELECT t2.id
  8  	   INTO   v_id
  9  	   FROM   tab2_mview t2
 10  	   WHERE  t2.id = r1.id
 11  	   AND	  CONTAINS
 12  		    (t2.depts,
 13  		     r1.dept_inclusion) > 0
 14  	   AND	  NOT CONTAINS
 15  		    (t2.depts,
 16  		     r1.dept_exclusion) > 0;
 17  	   v_ids.EXTEND;
 18  	   v_ids(v_ids.LAST) := v_id;
 19  	 EXCEPTION
 20  	   WHEN NO_DATA_FOUND THEN NULL;
 21  	 END;
 22    END LOOP;
 23    OPEN :g_ref FOR SELECT * FROM TABLE (v_ids);
 24  END;
 25  /

PL/SQL procedure successfully completed.

SCOTT@orcl12c> PRINT g_ref

COLUM
-----
E1
E3

2 rows selected.

SELECT  X.ID
  FROM  TABLE1,
        XMLTABLE(
                 '/ROWSET/ROW/ID'
                 PASSING DBMS_XMLGEN.GETXMLTYPE(
                                                'SELECT  ''' || ID || TO_CLOB(''' ID') ||
                                                '  FROM DUAL' ||
                                                '  WHERE (' || REGEXP_REPLACE(
                                                                              DEPT_INCLUSION,
                                                                              '(\d+)',
                                                                              'EXISTS (' ||
                                                                              '        SELECT  1' ||
                                                                              '          FROM  TABLE2' ||
                                                                              '          WHERE ID = ''' || ID || '''' ||
                                                                              '            AND DEPT = \1' ||
                                                                              '       )'
                                                                             ) ||
                                                '        )' ||
                                                '    AND NOT (' || REGEXP_REPLACE(
                                                                                  DEPT_EXCLUSION,
                                                                                  '(\d+)',
                                                                                  'EXISTS (' ||
                                                                                  '        SELECT  1' ||
                                                                                  '          FROM  TABLE2' ||
                                                                                  '          WHERE ID = ''' || ID || '''' ||
                                                                                  '            AND DEPT = \1' ||
                                                                                  '       )'
                                                                                 ) ||
                                                '            )'
                                               )
                 COLUMNS
                   ID VARCHAR2(10) PATH '.'
                ) X
/

ID
----------
E1
E3

SQL>