Dave Webster's Blog

Tinkering with Oracle

“What’s a bind variable?”

Posted by anotherdavewebster on August 26, 2010

It would be reasonable to assume that everything that could ever be written about bind variables has already been written. Certainly regarding the concept of them and why they are a good thing anyway. A little while back, I was digging around on a system which was suffering from very high parse rates. I did the normal stuff and took a look in v$sql:

SELECT   ( SELECT username
            FROM dba_users
           WHERE user_id = parsing_user_id ) username
       , parsing_user_id
       , SUBSTR( sql_text, 1, 30 )
       , COUNT( * )
       , MAX( TO_DATE( first_load_time, 'YYYY-MM-DD/HH24:MI:SS' ) )
       , MAX( last_active_time )
    FROM v$sql
GROUP BY parsing_user_id, SUBSTR( sql_text, 1, 30 )
  HAVING COUNT( * ) > 5

Sure enough, I found a couple of statements with many thousands of variations, differing only by a handful of source table names and many different literal values. A quick look at the code confirmed that we had a piece of dynamic SQL (to manage a few different source tables) which concatenated a literal value into the SQL string. Something along the lines of:

execute immediate 'insert into table_a select * from ' || l_tab_name || ' where id = ' || l_id;

“Great stuff” I thought, I’ll go and find the developer who wrote that piece of code and ask him to switch to using a bind variable for the id but obviously retain the dynamic nature of the source table name. So I wrote him a quick eMail asking him to use a bind variable and the response rather surprised me. When I tell people the story, I ask them what they think the most worrying answer would be. To a man, they all go for “What’s a bind variable?”. That would indeed be a worrying response, but not unprecedented. The response I actually got was “I have”.

2 Responses to ““What’s a bind variable?””

  1. graham oakes said

    LOL – I take it that’s when you directed him to asktom?

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 )

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: