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 13:07:49 -0600
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
Other related posts:
