RE: finding SQL that doesn't have bind variables
Date: Fri, 16 Mar 2012 13:07:49 -0600
You would want something like:
select regexp_replace('col1 = 99','(^|\s)[[:digit:]]+(\s|$)',' _at_ ') from dual
col1 = _at_
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
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
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
> 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 @.
> from test