Remove a certain string (hint) from SQL code in oracle

Question

I have a string (clob) that contains SQL query with hints, i need to remove those hints from the sql code.

so this:

select 
/*+ ALL_ROWS */ 
/* 2014-12-08 08:26:40.533 -6e99e394:14a2a127782:-466(TXN_ID:-1) */ 
ID, ORIGIN_TICKET_ID, LIFECYCLE_STATUS ...

should looks like:

select ID, ORIGIN_TICKET_ID, LIFECYCLE_STATUS ...

I tried to play with replace and REGEXP_REPLACE but with no luck so far.

Please provide the statements, that failed to deliver the expected result. — Abecee
– Abecee, Commented Dec 21, 2014 at 9:00
You seem to want to remove ordinary comments as well - not just hints. If so: Are all of them coming immediately after the SELECT ? Are all these statements stored as single lines, or might they have line breaks? Which version of Oracle? — Abecee
– Abecee, Commented Dec 21, 2014 at 9:07
Basically everything between /* and */ should be removed (hints), no need to handle comments, oracle is v11.2.0.2 — Avoral
– Avoral, Commented Dec 21, 2014 at 9:12
An example would be: SELECT REPLACE(main.c,'/*%*/') FROM MAIN; but the % doesn't do the job and i cant seem to find something equivalent to do that. — Avoral
– Avoral, Commented Dec 21, 2014 at 9:13
A hint is a comment immediately following the SELECT with an additional plus sign after the comments openening /*. In your example, the substring holding the date information is an ordinary comment, not a hint. — Abecee
– Abecee, Commented Dec 21, 2014 at 9:22

Abecee · Accepted Answer · 2014-12-21 10:16:13Z

1

Edited to handle the CLOB…

Something along

SELECT 'SELECT '
  || TRIM(SUBSTR(str, LENGTH(str) - INSTR(REVERSE(str), '/*') + 2)) cleanedStatement
FROM
  (SELECT DBMS_LOB.SUBSTR(main.c, 4000, 1) str FROM Main
  );

should provide the requested as long as the comments (hints or not) are all placed at the statements' start.

edited Dec 21, 2014 at 10:16

answered Dec 21, 2014 at 9:35

Abecee

2,4032 gold badges14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Avoral Over a year ago

The main.c column is a clob and the reverse doesnt work on that:

Avoral Over a year ago

The main.c column is a clob, the reverse function doesnt work on it: ORA-00932: inconsistent datatypes i will try to work along the lines of your example and try to find a workaround, but if you have any other idea that would be nice, Thanks !

Abecee Over a year ago

Noticed the CLOB too late for my original suggestion. Started to adjust accordingly. Might your statements be longer than 4,000 characters?

Collectives™ on Stack Overflow

Remove a certain string (hint) from SQL code in oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related