0

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?

2 Answers 2

1

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...

Sign up to request clarification or add additional context in comments.

Comments

0

See this SO for a similar problem and several solutions:

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.