Re: finding SQL that doesn't have bind variables

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Fri, 16 Mar 2012 13:13:01 -0400
Message-ID: <CAE-dsOK2zUiJNknKXYRS_=5N5nU+C_j5_7phx+uQs5cY06Cegg_at_mail.gmail.com>

that is a very good idea. so look for digits that have a space between them and a character. Anyone know how to do that with a regular expression? I just recently started digging into them. so if i have

select mycol1, mycol
from mytab
where hiscol1 = 12356

the 123456 will turn into a single _at_ and the other digits will not?

thanks to the guy with the tip fro the quote. That will make the sql a little cleaner.

On Fri, Mar 16, 2012 at 11:28 AM, Jackie Brock <J.Brock_at_cablelabs.com>wrote:

> You could look for cases where the characters between word boundaries are
> exclusively digits; that should avoid column names, which would be a mix of
> string characters and digits.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Dba DBA
> Sent: Friday, March 16, 2012 9:06 AM
> To: ORACLE-L
> Subject: finding SQL that doesn't have bind variables
>
> There is a 10 year old function that I think Tom Kyte put on the web
> called remove_constraints that will turn literal values into a single _at_
> sign. That is pretty slow because it has to loop through every character in
> every sql statement. So I re-wrote it with some regular expressions. I
> include a test case below.
> The down side to this and to Tom's Approach is that if you have a column
> with a digit in it, it turns that digit into an _at_ sign. This isn't a total
> killer, but I'd prefer to avoid it. I have over 1300 columns with digits in
> them.
> Anyone know how I could tinker with this to make it only look for digits
> on the right side of the where clause? I am not worried about literals in
> the select clause, but it would be nice if there is a way to do that also
> (though that would be much harder)
>
>
>
> create table test(
> mytest clob);
>
> insert into test values ('select 1 from mytable where x =
> '||chr(39)||'v'||chr(39)||' and y = '||chr(39)||'q'||chr(39)); insert into
> test values ('select 1 from mytable where x =1235 and y =987654 and z =
> 3'); insert into test values ('select 1 from mytabe where a =
> '||chr(39)||'xyz'||chr(39)); commit;
>
> -- sql is basically 2 parts. inner part gets changes anything between
> single quotes to an _at_(the translate turns the single quote to an @. I was
> having trouble passing single quotes to a regular expression so I just
> turned the quotes into an _at_.
> -- outer 2 regexp_replace change all numbers to a single _at_. First one
> turns all digits into _at_. This leaves 1 or more @. outer replace turns the
> multiple _at_ into a single @.
>
> select
>
> regexp_replace(regexp_replace(regexp_replace(translate(mytest,chr(39),'_at_'),'@[^@]+@','@'),'[[:digit:]]','@'),'(@){2,}','@')
> new
> from test
> /
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 16 2012 - 12:13:01 CDT