Re: finding SQL that doesn't have bind variables

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Fri, 16 Mar 2012 17:13:19 -0400
Message-ID: <CAE-dsOJt9=M5_hiE6hvY+i_6n4u2tBfLGwBoLCvdZieO+x=f+A_at_mail.gmail.com>

This is what I did because I got sick of looking at it. I have the nested regexp_replace, because I could not figure out how to go get rid of the digits, following an =,<,>, or space, but leave the =,<,>, or space. since it is possible to have literals without a space before them.

it is seriously ugly. I see you had to add the space back in also. So if I look for following an =, oracle also replaces the =.

select
regexp_replace(regexp_replace(regexp_replace(regexp_replace(mytest,'[ ][[:digit:]]{1,}',' ! '),'[=][[:digit:]]{1,}','= !'),

       '[>][[:digit]]{1,}','> !'),'[<]{{;digit:]]{1,}',' < !') result,mytest

select mycol1, mycol2 ,mycol3 from mytab2 where x= ! and y = !

The original value was:

select mycol1, mycol2 ,mycol3 from mytab2 where x=12345 and y = 45

On Fri, Mar 16, 2012 at 3:07 PM, Jackie Brock <J.Brock_at_cablelabs.com> wrote:

> You would want something like:
> select regexp_replace('col1 = 99','(^|\s)[[:digit:]]+(\s|$)',' _at_ ') from
> dual
>
> which returns:
> col1 = _at_
>
> -----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 11:13 AM
> To: ORACLE-L
> Subject: Re: finding SQL that doesn't have bind variables
>
> 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
>
>
>

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

Received on Fri Mar 16 2012 - 16:13:19 CDT