Oracle regexp_substr pattern for parallel hints in dynamic queries

Hello i have the following PL\SQL Block

DECLARE
    v_clob CLOB := 'This is a sample SQL query /* APPEND PARALLEL(table) hint */ with a hint. /* PARALLEL(table) hint */';
    v_new_clob CLOB;
BEGIN
    -- Replace the comment containing 'APPEND PARALLEL' with an empty string
    v_new_clob := REGEXP_REPLACE(v_clob, '/\*.*?APPEND\s+PARALLEL.*?\*/',null); /* '', 1, 0, 'i'*/

    -- Output the modified CLOB
    DBMS_OUTPUT.PUT_LINE(v_new_clob);
END;

Which has the result:

This is a sample SQL query  with a hint. /* PARALLEL(table) hint */

The purpose of the above pattern is to catch parallel hints that contain APPEND in dynamic sql queries.

I want to create a pattetn for REGEXP_REPLACE that catches parallel hints and ensures that DO NOT contain APPEND. So the result i need for the above CLOB would be:

This is a sample SQL query /* APPEND PARALLEL(table) hint */ with a hint.

Thanks in advance!

Solution

You can match:

(/\*.*?) - The start of the comment, in a capturing group;
( - The start of a capturing group;
(APPEND\s+PARALLEL\s*\(.*?\)) - The APPEND PARALLEL hint in a capturing group;
| - or;
PARALLEL\s*\(.*?\) - The PARALLEL hint (without APPEND);
) - The end of the second capturing group;
(.*?\*/) - The end of the comment, in a capturing group.

And replace it with \1\3\4, the 1st, 3rd and 4th capturing groups, which is everything except the case of a PARALLEL hint without APPEND.

Like this:

DECLARE
  v_clob CLOB :=
    'This is a sample SQL query /* APPEND PARALLEL(table) hint */'
    || ' with a hint. /* PARALLEL(table) hint */';
  v_new_clob CLOB;
BEGIN
  v_new_clob := REGEXP_REPLACE(
    v_clob,
    '(/\*.*?)((APPEND\s+PARALLEL\s*\(.*?\))|PARALLEL\s*\(.*?\))(.*?\*/)',
    '\1\3\4'
  ); /* '', 1, 0, 'i'*/

  -- Output the modified CLOB
  DBMS_OUTPUT.PUT_LINE(v_new_clob);
END;
/

Which outputs:

This is a sample SQL query /* APPEND PARALLEL(table) hint */ with a hint. /*  hint */

fiddle

If you want to remove the entire comment then:

DECLARE
  v_clob CLOB :=
    'This is a sample SQL query /* APPEND PARALLEL(table) hint */'
    || ' with a hint. /* PARALLEL(table) hint */';
  v_new_clob CLOB;
BEGIN
  v_new_clob := REGEXP_REPLACE(
    v_clob,
    '((/\*.*?APPEND\s+PARALLEL\s*\(.*?\).*?\*/)|/\*.*?PARALLEL\s*\(.*?\).*?\*/)',
    '\2'
  ); /* '', 1, 0, 'i'*/

  -- Output the modified CLOB
  DBMS_OUTPUT.PUT_LINE(v_new_clob);
END;
/

Which outputs:

This is a sample SQL query /* APPEND PARALLEL(table) hint */ with a hint.

fiddle