Hello,
I have a table with a CLOB column whereby, Analysts save their raw SQL query texts so that it can be parsed and executed by my custom procedure programmatically, scheduled to run at a given time in Oracle 19.11.
Analysts mainly enter their raw SQL query texts starting with either SELECT or WITH words and any other words that begin other than these two words are not allowed.
While I can't ensure the completeness and integrity of the SQL query text entered by the Analysts due to human error, what I'd like to do is to focus on and ensure that any erroneously entered optimizer hints are corrected by the procedure before executing the query.
As an example, I have the following erroneous raw query text entered by a user:-
DECLARE
s_raw_text CLOB;
n_parallel_degree NUMBER DEFAULT 8;
BEGIN
s_raw_text:= '
select /*+ parallel(8) PARALLEL(16) parallel(16) INDEX_COMBINE */ *
from (
select /*+ parallel(32) */ *
from (
SELECT /*+ parallel_index(32) parallel(32) */ *
FROM table1 t1
left join table2 t2 on t1.col1 = t2.col2
WHERE col3 = col4
where 1=1
and col1 = col2
)
where 1=1
and col5 = col6;
';
dbms_output.put_line('s_raw_text: ' || s_raw_text);
END;
Here's what I'd like the procedure to do:-
1. Look for all the occurrences of optimizer hint strings, if they're found. That is, look for the presence of any text between '/*+' and '*/' strings and extract them and strictly work with this extracted text.
2. Upper-case all the extracted optimizer hint strings only. (i.e. only the text in between '/*+' and '*/' strings).
3. In the extracted strings, look for any string that matches something like '(N)' text and if found, replace that N with the number represented by the n_parallel_degree variable's default value of 8. So this would mean that in the the outer-most query, the hint text (already upper-cased) of
/*+ PARALLEL(8) PARALLEL(16) PARALLEL(16) INDEX_COMBINE */ would become
/*+ PARALLEL(8) PARALLEL(8) PARALLEL(8) INDEX_COMBINE */
And in the second sub-query, the hint text of
/*+ PARALLEL(32) */ would become /*+ PARALLEL(8) */
And in the third sub-query, the hint text of
/*+ PARALLEL_INDEX(32) PARALLEL(32) */ would become
/*+ PARALLEL_INDEX(8) PARALLEL(8) */.
3. Finally, all the repeating/duplicate hint words should be removed and only unique hint words should remain, as shown in the desired end-result:-
select /*+ PARALLEL(8) INDEX_COMBINE */ *
from (
select /*+ PARALLEL(8) */ *
from (
SELECT /*+ PARALLEL_INDEX(8) PARALLEL(8) */ *
FROM table1 t1
left join table2 t2 on t1.col1 = t2.col2
WHERE col3 = col4
where 1=1
and col1 = col2
)
where 1=1
and col5 = col6;
4. Note in the above desired result that ONLY the hint strings have been upper-cased but not the rest of the raw text, as we would like to preserve the casing of the rest of the string, in case there is something like column LIKE '%dop%' in the where clause somewhere.
Thanks in advance!