Dica PL/SQL: Criando uma lista IN dinâmica

Boa Tarde pessoal! Vamos a mais uma de PL/SQL que é interessante...

Uma situação que ocorre com frequência em aplicativos ADF é quando precisamos criar uma lista de itens ou checkboxes, em que o usuário seleciona quantos quiser e precisamos montar uma consulta baseada nestes checkboxes. Por exemplo, se o usuário seleciona os checkboxes A, B e C precisamos montar na consulta a cláusula:

AND tabela.campo IN('A','B','C')

Sendo que não sabemos exatamente quantas opções virão marcadas. Uma solução interessante para este caso é o uso do CAST e da função THE no PL/SQL, com o qual fazemos uma cláusula IN aceitar uma TABLE OF VARCHAR2. Aí é só popular esta table com os valores dinâmicos e passar pra consulta!

Primeiro, criamos o tipo:
CREATE TYPE t_empTab
IS TABLE OF VARCHAR2(50); <-- Tem que ser type do banco, não local ou de package

Depois, fazemos uma procedure PL/SQL que pode nos retornar um REF CURSOR por exemplo.

DECLARE
v_empTab t_empTab := t_empTab();
CURSOR c_empTab IS
SELECT empno
FROM scott.emp
WHERE ename IN
(SELECT * FROM THE(SELECT cast(v_empTab AS t_empTab) from dual));
v_empNo c_empTab%ROWTYPE;
BEGIN
v_empTab.EXTEND;
v_empTab(v_empTab.LAST) := 'SMITH';
v_empTab.EXTEND;
v_empTab(v_empTab.LAST) := 'ALLEN';

OPEN c_emp_tab;
RETURN c_emp_tab;
END;

É isso pessoal! Abraços!