Oracle regexp_substr pattern for parallel hints in dynamic queries

Question

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!

• Paul W: Keep in mind, that if parallel dml is enabled (either by hint enabled_parallel_dml or by session setting (alter session enable parallel dml), a pdml enabled insert will append even without the append hint, even with no hints at all (if the table itself is marked with parallel degree). If your goal is to prevent space allocation above the high water mark, or to prevent exclusive locks on the whole table, you'd have to prevent pdml as well as serial append. It would be better to fix code at its source than to try to detect and strip hints on the fly and change them in flight.
• Paul W: You can also simply disable all hints with alter session set optimizer_ignore_hints=true. But there may be hints you need. Maybe if you shared the reason why you want to strip append out, and why you can't address it at the source, we could provide some alternative suggestions.

Answer 1

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

• Panos_Koro: Thank you very much but the result i need is: This is a sample SQL query /* APPEND PARALLEL(table) hint */ with a hint. Which means i need to replace all parallel hints that they don't contain the word APPEND including their comment start and end /* */ , with NULL. Can you help me achieve this?
• MT0: @Panos_Koro Updated