Blog do Marcio Portes

Participo ativamente de várias listas de Oracle e algumas vezes, vejo perguntas seguidamente aparecendo. Uma delas é sobre como usar uma lista de valores separadas por vírgula. No exemplo abaixo, a pessoa recebia uma lista e queria comparar com valores em uma tabela.

"Não estou conseguindo montar o sql para fazer o seguinte. Eu recebo
uma sequencia com n códigos (digamos 1,2,3,4,5,6,7,8) e tenho uma
tabela com vários códigos(2,3,4,45,36,26,74).

Preciso saber como seria o SQL para eu descobrir quais códigos da
sequencia que eu recebo não estão na tabela. (Neste caso 1,5,6,7,8)

Vamos ao exemplo, por favor se houver dúvida não hesite em comentar.

ops$marcio@LNX10GR2> create table t ( x number );

Table created.

ops$marcio@LNX10GR2> insert into t values (2);

1 row created.

ops$marcio@LNX10GR2> insert into t values (3);

1 row created.

ops$marcio@LNX10GR2> insert into t values (4);

1 row created.

ops$marcio@LNX10GR2> insert into t values (45);

1 row created.

ops$marcio@LNX10GR2> insert into t values (36);

1 row created.

ops$marcio@LNX10GR2> insert into t values (26);

1 row created.

ops$marcio@LNX10GR2> insert into t values (74);

1 row created.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace type array as table of varchar2 (50);
  2  /

Type created.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace function f ( p_string in varchar2 )
  2  return array
  3  is
  4      l_string        varchar2(50) default p_string || ',';
  5      l_data          array := array();
  6      n               number;
  7  begin
  8    loop
  9        exit when l_string is null;
 10        n := instr( l_string, ',' );
 11        l_data.extend;
 12        l_data(l_data.count) :=
 13           ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
 14        l_string := substr( l_string, n+1 );
 15    end loop;
 16    return l_data;
 17  end;
 18  /

Function created.

ops$marcio@LNX10GR2> show error
No errors.
ops$marcio@LNX10GR2> select * from t;

            X
-------------
            2
            3
            4
           45
           36
           26
           74

7 rows selected.

ops$marcio@LNX10GR2> select cast( column_value as number ) x
  2    from table( cast( f('1,2,3,4,5,6,7,8') as array ) )
  3  /

            X
-------------
            1
            2
            3
            4
            5
            6
            7
            8

8 rows selected.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> select cast( column_value as number ) x
  2    from table( cast( f('1,2,3,4,5,6,7,8') as array ) )
  3  minus
  4  select x from t;

            X
-------------
            1
            5
            6
            7
            8

5 rows selected.