finding SQL that doesn't have bind variables
From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Fri, 16 Mar 2012 11:05:57 -0400
Message-ID: <CAE-dsOLE_Yr4zG1qVg22f8Q2w_KtENHB_YqX+6R7j=MxCn+voA_at_mail.gmail.com>
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)
Date: Fri, 16 Mar 2012 11:05:57 -0400
Message-ID: <CAE-dsOLE_Yr4zG1qVg22f8Q2w_KtENHB_YqX+6R7j=MxCn+voA_at_mail.gmail.com>
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-lReceived on Fri Mar 16 2012 - 10:05:57 CDT