Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: String search and replace in pl/sql
Jeremy wrote:
>
> 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
Put the code into a package and use a global variable to retain either the last order by or, if 10g, its hash for comparison purposes.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Fri Feb 10 2006 - 11:11:59 CST