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


    l_query long;

    l_char  varchar2(1 char);

    l_in_quotes boolean default FALSE;


    for i in 1 .. length( p_query )


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

        if ( l_char = ”” and l_in_quotes )


            l_in_quotes := FALSE;

        elsif ( l_char = ”” and NOT l_in_quotes )


            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);



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





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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: