How-to find sqls NOT using bind variables?

Sometimes we need to get to know which sqls do not use bind variables. Here we can see how to find it out by using procedures.

 

create table t1 as select sql_text from v$sqlarea;

 

alter table t1 add sql_text_wo_constants varchar2(1000);

 

create or replace function

remove_constants( p_query in varchar2 ) return varchar2

as

    l_query long;

    l_char  varchar2(1 char);

    l_in_quotes boolean default FALSE;

begin

    for i in 1 .. length( p_query )

    loop

        l_char := substr(p_query,i,1);

        if ( l_char = ”” and l_in_quotes )

        then

            l_in_quotes := FALSE;

        elsif ( l_char = ”” and NOT l_in_quotes )

        then

            l_in_quotes := TRUE;

            l_query := l_query || ”’#’;

        end if;

        if ( NOT l_in_quotes ) then

            l_query := l_query || l_char;

        end if;

    end loop;

    l_query := translate( l_query, ‘0123456789’, ‘@@@@@@@@@@’ );

    for i in 0 .. 8 loop

        l_query := replace( l_query, lpad(‘@’,10-i,’@’), ‘@’ );

        l_query := replace( l_query, lpad(‘ ‘,10-i,’ ‘), ‘ ‘ );

    end loop;

    return upper(l_query);

end;

/

update t1 set sql_text_wo_constants = remove_constants(sql_text);

 

select sql_text_wo_constants, count(*)

  from t1

 group by sql_text_wo_constants

having count(*) > 100

 order by 2

/

 

Reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:1994553100566534::::P11_QUESTION_ID:1163635055580

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How-to find sqls NOT using bind variables?

  1. neworacledba says:

    this is a great command that can be automated in form on sql script and used for operational purposes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: