Arrays in the clause 'NOT IN' WHERE

Question

DECLARE 
   string_valor  VARCHAR2(100);
   lista_array dbms_utility.lname_array;
   contador binary_integer; 
BEGIN 
   SELECT t.campo_a INTO string_valor
   FROM tabela t
   WHERE t.campo_cod = 1;
   dbms_utility.comma_to_table
   (
       list          => regexp_replace(string_valor,'(^|,)','\1x')
       , tablen  => contador
       , tab       => lista_array
   );       
   FOR i in 1 .. l_count LOOP
      dbms_output.put_line( substr(lista_array(i),2) );
   END LOOP;
END;

This code works perfectly, what it does is take a variable VARCHAR with values '2,3,5,9' for example and separate them into a vector of numbers, for what this need? So that I can compare, using NOT IN, each ARRAY element with a NUMBER field within the WHERE clause.

The problem is how can I call it in the Where clause, using NOT IN:

SELECT ... FROM tabela t
WHERE campo_exemplo_1 > 0 
      AND campo_exemplo_2 <> campo_exemplo_4
      AND campo_exemplo_3 NOT IN ( ARRAY AQUI??? )            

Answer 1

The clause NOT IN must be filled with the values you want to search in the field.

For example, if the field is a foreign key, make a subquery by selecting only the ids of the table.

Table post

id titulo
 1 Titulo do post 1
 2 Titulo do post 2

Table Category

id titulo
 1 Curiosidades
 2 Bobagens
 3 Variedades

Table post_category

post_id category_id
      1           1
      1           3
      2           2
      2           3

Your query, then, would look like this:

SELECT p.*
FROM post p
INNER JOIN post_category pc
    ON pc.post_id = p.id
    AND pc.category_id IN (
        SELECT c.id
        FROM category c
        WHERE c.titulo = 'Variedades'
    )

PS: I based my answer on the ANSI SQL standard. Maybe Oracle has some differences in the implementation of IN but I find it unlikely.

Answer 2

Searching the old google and gathering information, I managed to solve the problem, I will leave posted here the result in case someone needs.

DECLARE 
    V_varchar2  VARCHAR2(100);
    l_List_char dbms_utility.lname_array;
    l_count binary_integer;   

    TYPE TesteVarchar2 IS TABLE OF NUMBER;
    l_Aux_ListNumber TesteVarchar2  := TesteVarchar2();
    resultado NUMBER;

BEGIN      
    V_varchar2 := '2,9,8,10,5';
    dbms_utility.comma_to_table
    (
         list      => regexp_replace(V_varchar2,'(^|,)','\1x')
         , tablen  => l_count
         , tab     => l_List_char
    ); 

    l_Aux_ListNumber.EXTEND(l_count);

    FOR i in 1 .. l_count LOOP       
         l_Aux_ListNumber(i) := substr(l_List_char(i),2);
    END LOOP;

    -- Aqui uma pequena operação matematica para provar que virou number
    FOR i in 1 .. l_count LOOP
         dbms_output.put_line( l_Aux_ListNumber(i));
         resultado := l_Aux_ListNumber(i) * i;
         dbms_output.put_line( 'Result: ' || resultado );   
    END LOOP;

END;

Then just call inside clause:

Where na clausula NOT IN ( SELECT column_value FROM TABLE( TesteVarchar2() );