using sys_context to match data string

Question

I'm trying to use data in sys_context form to perform a match in a WHERE clause.

What I put into the context is ('53','89'), which is what is returned when I select against dual.

My where statement is: where to_char(location_id) in sys_context('my_ctx','valoc')

Since I'm not getting the expected response, I'm guessing that what I think Oracle should see is not actually what it sees, but I don't know how to "look" at what's passed to the processor from TOAD.

The original form was where location_id in sys_context('my_ctx','valoc') with (53,89) in valoc, but that didn't return anything either. I'm sensing there may be no answer to my problem.

Przemyslaw Kruglej · Accepted Answer · 2013-10-23 20:20:59Z

1

The problem is that the resulting WHERE clause is equivalent to this:

where to_char(location_id) in '('53','89')'

(didn't double the inner apostrophes for clarity)

The database sees what's retrieved from context as a single value, not as a list of values.

You can use the CONNECT BY trick to achieve your goal:

SELECT 1
  FROM dual
WHERE '53' IN ( -- replace '53' with TO_CHAR(location_id)
  SELECT regexp_substr('53,89', '[0-9]*', 1, level) -- replace '53,89' with sys_context('my_ctx','valoc')
    FROM dual
  CONNECT BY regexp_substr('53,89', '[0-9]*', 1, level) IS NOT NULL -- replace '53,89' with sys_context('my_ctx','valoc')
);

edited Oct 23, 2013 at 20:20

answered Oct 23, 2013 at 20:15

Przemyslaw Kruglej

8,1312 gold badges30 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

using sys_context to match data string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related