FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE table_name (value) AS
  SELECT q'{nvl(replace('$$TAG1$$$$TAG2$$$$TAG3$$' ...) ... $$TAG4$$ = '12345678' ... INSTR('product1, product2,',',$$TAG5$$,') ... GET_CODE($$TAG6$$, 'something')}' FROM DUAL;

SELECT s.*
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT LISTAGG(
                  CASE
                  WHEN term LIKE '$$%$$'
                  AND  MOD(num_quotes,2) = 0
                  THEN '''' || term || ''''
                  ELSE term
                  END,
                  NULL
                ) WITHIN GROUP (ORDER BY pos) AS value
         FROM   (
           SELECT REGEXP_SUBSTR(t.value, q'!'|\$\$.*?\$\$|(\$?[^'$]+)+!', 1, LEVEL) AS term,
                  COUNT(
                    CASE
                    WHEN REGEXP_SUBSTR(t.value, q'!'|\$\$.*?\$\$|(\$?[^'$]+)+!', 1, LEVEL) = ''''
                    THEN 1
                    END
                  ) OVER (ORDER BY LEVEL) AS num_quotes,
                  LEVEL AS pos
           FROM   DUAL
           CONNECT BY LEVEL <= REGEXP_COUNT(t.value, q'!'|\$\$.*?\$\$|(\$?[^'$]+)+!')
         )
       ) s;

VALUE
nvl(replace('$$TAG1$$$$TAG2$$$$TAG3$$' ...) ... '$$TAG4$$' = '12345678' ... INST R('product1, product2,',',$$TAG5$$,') ... GET_CODE('$$TAG6$$', 'something')