RE: finding SQL that doesn't have bind variables
- From: Jackie Brock <J.Brock@xxxxxxxxxxxxx>
- To: "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Mar 2012 15:36:41 -0600
I was being lazy... I'm used to doing my regexes in PERL... :)
Try this one:
select regexp_replace('col1 = 99','(^|\s|=|<|>)([[:digit:]]+)(\s|$)','\1@\3')
from dual;
REGEXP_REPLACE('COL1=99','(^|\S|=|<|>)([[:DIGIT:]]+)(\S|$)','\1@\3')
--------------------------------------------------------------------
col1 = @
1 row selected.
REGEXP_REPLACE('COL1>99','(^|\S|=|<|>)([[:DIGIT:]]+)(\S|$)','\1@\3')
--------------------------------------------------------------------
col1>@
1 row selected.
REGEXP_REPLACE('COL1=99','(^|\S|=|<|>)([[:DIGIT:]]+)(\S|$)','\1@\3')
--------------------------------------------------------------------
col1=@
1 row selected.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Dba DBA
Sent: Friday, March 16, 2012 3:13 PM
To: ORACLE-L
Subject: Re: finding SQL that doesn't have bind variables
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@xxxxxxxxxxxxx> wrote:
> You would want something like:
> select regexp_replace('col1 = 99','(^|\s)[[:digit:]]+(\s|$)',' @ ')
> from dual
>
> which returns:
> col1 = @
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> 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 @ 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@xxxxxxxxxxxxx
> >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@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> > 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 @ 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 @ 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 @(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 @.
> > -- outer 2 regexp_replace change all numbers to a single @. First
> > one turns all digits into @. This leaves 1 or more @. outer replace
> > turns the multiple @ into a single @.
> >
> > select
> >
> > regexp_replace(regexp_replace(regexp_replace(translate(mytest,chr(39
> > ),
> > '@'),'@[^@]+@','@'),'[[:digit:]]','@'),'(@){2,}','@')
> > new
> > from test
> > /
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
