Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: String search and replace in pl/sql

Re: String search and replace in pl/sql

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 10 Feb 2006 09:11:59 -0800
Message-ID: <1139591519.391016@jetspin.drizzle.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US