Oracle : replace string of options based on data set – is this possible?

I have column in table looking like this:

PATTERN
{([option1]+[option2])*([option3]+[option4])}
{([option1]+[option2])*([option3]+[option4])*([option6]+[option7])}
{[option1]+[option6]}
{([option1]+[option2])*([option8]+[option9])}
{([option1]+[option2])*[option4]}
{[option10]}

Every option has a number of value. There is a table – let’s call it option_set and records look like

OPTION      VALUE
option1     3653265
option2     26452
option3     73552
option3     100
option4     1235
option5     42565
option6     2330
option7     544
option9     2150

I want to replace option name to number in 1st table, if exists of course, if not exists then =0. I have done this in PLSQL (get the pattern, go through every option, and if exists – regexp_replace), but I am wondering if this could be done in SQL?? My goal is to replace values for all patterns for current OPTION_SET and get only records, where all equations would be greater than 0. Of course – I couldn’t run this equation in SQL, so I think of something like

for rec in
(
  SELECT...
)
loop
  execute immediate '...';
  if above_equation > 0 then ..
end loop;

Any ideas would be appreciated

Answer

You can do a loop-like query in SQL with the recursive CTE, replacing new token on each iteration, so this will let you to replace all the tokens. The only way I know to execute a dynamic query inside SQL statement in Oracle is DBMS_XMLGEN package, so you can evaluate the expression and filter by the result value without PL/SQL. But all this is viable for low cardinality tables with patterns and options.

Here’s the code:

with a as (
  select 1 as id, '{([option1]+[option2])*([option3]+[option4])}' as pattern from dual union all
  select 2 as id, '{([option1]+[option2])*([option3]+[option4])*([option6]+[option7])}' as pattern from dual union all
  select 3 as id, '{[option1]+[option6]}' as pattern from dual union all
  select 4 as id, '{([option1]+[option2])*([option8]+[option9])}' as pattern from dual union all
  select 5 as id, '{([option1]+[option2])*[option4]}' as pattern from dual union all
  select 6 as id, '{[option10]}]' as pattern from dual
)
, opt as (
  select 'option1' as opt, 3653265 as val from dual union all
  select 'option2' as opt, 26452 as val from dual union all
  select 'option3' as opt, 73552 as val from dual union all
  select 'option3' as opt, 100 as val from dual union all
  select 'option4' as opt, 1235 as val from dual union all
  select 'option5' as opt, 42565 as val from dual union all
  select 'option6' as opt, 2330 as val from dual union all
  select 'option7' as opt, 544 as val from dual union all
  select 'option9' as opt, 2150 as val from dual
)
, opt_ordered as (
  /*Order options to iterate over*/
  select opt.*, row_number() over(order by 1) as rn
  from opt
)
, rec (id, pattern, repl_pattern, lvl) as (
  select
    id,
    pattern,
    pattern as repl_pattern,
    0 as lvl
  from a
  
  union all
  
  select
    r.id,
    r.pattern,
    /*Replace each part at new step*/
    replace(r.repl_pattern, '[' || o.opt || ']', o.val),
    r.lvl + 1
  from rec r
    join opt_ordered o
      on r.lvl + 1 = o.rn
)
, out_prepared as (
  select
    rec.*,
    case
      when instr(repl_pattern, '[') = 0
      /*When there's no more not parsed expressions, then we can try to evaluate them*/
      then dbms_xmlgen.getxmltype(
        'select ' || replace(replace(repl_pattern, '{', ''), '}', '')
        || ' as v from dual'
      )
      /*Otherwise SQL statement will fail*/
    end as parsed_expr
  from rec
  /*Retrieve the last step*/
  where lvl = (select max(rn) from opt_ordered)
)
select
  id,
  pattern,
  repl_pattern,
  extractvalue(parsed_expr, '/ROWSET/ROW/V') as calculated_value
from out_prepared o
where extractvalue(parsed_expr, '/ROWSET/ROW/V') > 0

ID | PATTERN                                                             | REPL_PATTERN                              | CALCULATED_VALUE
-: | :------------------------------------------------------------------ | :---------------------------------------- | :---------------
 1 | {([option1]+[option2])*([option3]+[option4])}                       | {(3653265+26452)*(73552+1235)}            | 275194995279    
 2 | {([option1]+[option2])*([option3]+[option4])*([option6]+[option7])} | {(3653265+26452)*(73552+1235)*(2330+544)} | 790910416431846 
 3 | {[option1]+[option6]}                                               | {3653265+2330}                            | 3655595         
 5 | {([option1]+[option2])*[option4]}                                   | {(3653265+26452)*1235}                    | 4544450495      

db<>fiddle here