RE: finding SQL that doesn't have bind variables

From: Jackie Brock <J.Brock_at_cablelabs.com>
Date: Fri, 16 Mar 2012 13:07:49 -0600
Message-ID: <3C0068AB22D70B4DA32B9A2A965968C77FD9206F91_at_srvxchg>

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 - 14:07:49 CDT