String search and replace in pl/sql

From: Jeremy <jeremy0505_at_gmail.com>
Date: Fri, 10 Feb 2006 09:13:46 -0000
Message-ID: <MPG.1e56699d5f92dcf798a0e7@news.individual.net>

Oracle 9iR2

Take the following string (it is a piece of SQL)

select c1,c2
from (select c1,c2

        from   tab
        order by 1)

where rownum <=100
order by 2

Now this could be a piece of dynamic SQL and we want to programatically alter the "order by" at the end of the statement with a new "order by" based on other criteria.

The question is: what is the easiest way of identifying the last "order by" so that we can go about stripping it off and replacing it with another? The difficulties I see are that the statement may not have ANY final "order by" - so the first challenge is to identify whether the outermost level of the SQL has an "order by" and then identify its start position (easy with INSTR using the -1 option to count from the end of the string).

Appreciate any suggestions.

cheers

-- 

jeremy

Received on Fri Feb 10 2006 - 03:13:46 CST