Variables in a Context variable

Question

Hi, I have a table and I would like to use a context variable to select from that table.

Table Key, data 'XX', 'BLAbla' 'yy', 'blaBla' 'zz', 'bLaBla' 'aa', 'lkdjfa' ..... .... ..

My selection is :

select * from Table where key is not in ('XX','zz');

My definition of the context variable is like

Variable := '('||'''xx'''||','||'''yy'''||')';

DBMS__SESSION.SET_CONTEXT('key_context', 'KeyValues', Variable );

Select sys_context('key_context','KeyValues') Result from dual;

Result ('XX','zz')

So I thouhgt this would work:

select * from Table where key is not in sys_context('key_context','KeyValues');

Any suggestions?

Answer 1

Hi Andrjes,

See this SO for a similar problem and several solutions:

• Oracle Parameters with IN statement?

Answer 2

What you need is to pass a single string into IN(). You can do it by the following code, borrowed from this AskTom question:

create or replace type myTableType as table of varchar2(100);
/

create or replace function in_list( p_string in varchar2 )
return myTableType
as
        l_data         myTableType := myTableType();
        l_string       long default p_string || ',';
        l_n            number;
begin
        loop
                exit when l_string is null;
                l_data.extend;
                l_n := instr( l_string, ',' );
                l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
                l_string := substr( l_string, l_n+1 );
        end loop;
        return l_data;
end;
/

select *
  from Table
 where key is not in (
         select * from THE (
           select cast(in_list(sys_context('key_context','KeyValues')) as mytableType)
             from dual
         )
       );

But probably it's simplier to keep keys in a table...