Re: Bind Variables

A copy of this was sent to "Richard Fairbairn" <zenith_at_technologist.com> (if that email address didn't require changing) On Mon, 12 Oct 1998 09:34:08 +0100, you wrote:

>Can anyone please tell me exactly what a bind variable is? I am also
>interested in the etymology of the name "bind variable". I am most familiar
>with Forms 4.5 and Oracle 7.3 running on Win 95 / NT.
>
>Thanks
>
>R.W. Fairbairn
>

A bind variable is like a parameter to a query. For example the query:

SQL> select * from emp where ename = 'KING';

make no use of bind variables. If we want to change the search criteria from 'KING' to 'BLAKE' we must rewrite the entire query, reparse and execute it.

If on the other hand we had coded:

SQL> select * from emp where ename = :x;

We would 'bind' the value of 'KING' to :x and simply parse and execute the query. Later we can change the value of our bind variable :x to BLAKE and simply re-execute the query (skipping the parse phase).

To a forms developer, this is all pretty much transparent. D2K and pl/sql do this transparently for you. When you build a block and go into enter query mode, forms will build a query that uses bind variables (in the hope that someone else has already parsed/optimized such a query for us in the shared pool in the database and we can save time). also, whenever you code pl/sql, the pl/sql parser is finding bind variables and turning them into 'real' bind variables when possible. Consider the following example executed in sql*plus:

SQL> alter session set sql_trace =true; Session altered.

SQL> _at_a
SQL> declare

  2       l_cnt   number;
  3       l_str   varchar2(20) default 'KING';
  4  begin
  5       select count(*) into l_cnt from emp where ename = l_str;

PL/SQL procedure successfully completed.

Now, when we run 'tkprof' on the generated trace file (from the alter session command) we get to see the 'real' sql we executed which was:

SELECT COUNT(*)
FROM
 EMP WHERE ENAME = :b1

See how pl/sql rewrote the query using placeholders AKA bind variables in the query.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.