How to do a select on a table based on a index-by table? I have a table called getSalas:

CREATE TABLE getSalas(
    ID_SALA NUMBER(4),
    NOME_CARATERISTICA VARCHAR2(20),
    VALOR_CARATERISTICA NUMBER(4)
);


The contents of that table are like this:


  1, 'Pool', 2
  
  1, 'A/C', 1
  
  2, 'A/C', 2
  ...


Which would mean that the room with the ID_SALA 1 has 2 pools and A/C. And the room with ID_SALA 2 has 2 A/C's.
I've also created the following datatypes:

CREATE OR REPLACE TYPE c_valor FORCE IS OBJECT (CARATERISTICA VARCHAR2(20), VALOR NUMBER(30));
/
CREATE OR REPLACE TYPE tabc_valor FORCE IS TABLE OF c_valor;
/


So my problem is, I need to know if a given set of characteristics exists in a room (e.g Has at least 2 pools and 3 unicorns etc...). For that I've created the following procedure:

CREATE OR REPLACE PROCEDURE getSalas_Carateristicas
(v_carateristicas IN tabc_valor, lista OUT SYS_REFCURSOR)
IS BEGIN
    OPEN lista FOR SELECT getSalas.ID_SALA
    FROM getSalas, TABLE(v_carateristicas) v_carateristicas
    WHERE getSalas.NOME_CARATERISTICA = v_carateristicas.CARATERISTICA
    AND getSalas.VALOR &gt;= v_carateristicas.VALOR
    GROUP BY getSalas.ID_SALA
    ORDER BY getSalas.ID_SALA;
END;
/


v_carateristicas is where I receive the set of characteristics, I would look something like this:


  'A/C', 1
  
  'Extinguisher', 2
   ...


The problem is that the procedure is returning rooms that have match one or more of the set of characteristics, not all! For example, if I make v_carateristicas equal to:


  'Pool', 2
  
  'A/C', 2


And if getSalas looks like this:


  1, 'Pool', 2
  
  1, 'A/C', 1
  
  2, 'A/C', 2


It will return both rooms, although only room with the ID_SALA 1 matches what is asked.
This can be broken out into different steps if you want (i.e. variable for the count from the characteristics table).  But something with this logic should work.  Assuming unique constraint on (ID_SALA, NOME_CARATERISTICA) in getSalas, and unique data/constraint on (CARATERISTICA) in tabc_valor or whatever source data you have to compare to.  If those aren't necessarily unique, then you will only have one extra step to group by those columns first and sum the counts.

In one query:

with matches as (
    select gs.ID_SALA, count(1) ct
    from getSalas gs
    join tabc_valor tv on tv.CARATERISTICA = gs.NOME_CARATERISTICA
        and tv.VALOR &lt;= gs.VALOR_CARATERISTICA
    group by gs.ID_SALA
), targetCount as (
    select count(1) ct
    from tabc_valor
)
select m.ID_SALA
from matches m
join targetCount tc on tc.ct = m.ct;