0
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??? )
Thank you, however this was the first solution that comes to mind, however select is very complex and has several OR within Where, in this case they will all have to do the same subselect, so I went to ARRAY.
– Marcos