Oracle: SELECT value from where clause as dummy table

after a lot of searching I unfortunately couldn’t find a solution for the following task:

I need an Oracle-SQL-Query that returns the value from the WHERE-clause as the result value. This sounds easy, but I just couldn’t find out how to achieve this.

Example (how it should be):

SELECT some_dummy_colum FROM some_dummy_table WHERE some_dummy_coumn = 'MY_VALUE';

Desired Result:

MY_VALUE

I know that I could write something like

SELECT 'MY_VALUE' FROM DUAL;

But in this case I cannot hard-code the ‘MY_VALUE’, it can only be provided in the WHERE-clause (not in the SELECT or FROM parts or elsewhere).

Is there any way to achieve this in Oracle? Thanks in advance for ideas!

Answer

Here’s a really nasty hack, more for curiosity than anything:

select (
  select regexp_replace(vsql.sql_text,
    q'@.*where '(.*)' is not null@', '1')
  from v$session vses
  join v$sql vsql
  on vsql.sql_id = vses.sql_id
  where vses.audsid = userenv('SESSIONID') 
) as result
from dual
where 'MY_VALUE' is not null
/

RESULT              
--------------------
MY_VALUE 

Or without the subquery, if you can make the where clause more complicated:

select regexp_replace(vsql.sql_text,
  q'@.*and '(.*)' is not null@', '1') as result
from v$session vses
join v$sql vsql
on vsql.sql_id = vses.sql_id
where vses.audsid = userenv('SESSIONID') 
and 'MY_VALUE2' is not null
/

RESULT              
--------------------
MY_VALUE2 

Either way it’s looking in the data dictionary for the statement that is currently being executed by this session; and I’m not entirely sure that’s a valid thing to do. This seems to work in 11g and 12c, and (so far) always reports the value being searched for, but I don’t know that it is guaranteed. It feels like an abuse of the space-time continuum…