OracleBlog

If you learn nothing else from the Oracle experts out there but want to improve your Oracle application development knowledge, then read up on bind variables.

Bind variables are already very well explained, here are my favourite links on the subject:

Mark Rittman's Bind Variables Explained
http://www.rittmanmead.com/2004/03/24/bind-variables-explained/

Tom Kyte is one of the champions of using Bind Variables. Links to his articles and books can be found at the bottom of Mark's article.

After reading these articles, I understood that PL/SQL automatically binds variables, so you really don't have to worry. But I was still concerned that I might be missing something.

Tom has a query that you can run to determine if you are currently using bind variables or not. Find it here (Hint: select sql_test from v$sqlarea):
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1163635055580.

I posted the following question to the Dizwell forum, which is an excellent place to tap the minds of Oracle gurus directly.

http://www.phpbbserver.com/phpbb/viewtopic.php?t=169&mforum=dizwellforum
Note: no longer available

Is there any possible way to NOT use bind variables in a PL/SQL stored procedure WITHOUT using 'execute immediate' or DBMS_SQL package?

I just want to make sure that there are only 2 situations I have to look out for in my PL/SQL code with regards to bind variables.

A developer from Australia going by "gamyers" responded with ref cursors:

DECLARE
v_cur sys_refcursor;
BEGIN
OPEN v_cur FOR 'select 1 from dual where 1=2';
CLOSE v_cur;
END;

Also, you could code
IF v_value =1 then
select ... where col_a = 1;
ELSIF v_value = 2 then
select ... where col_a = 2;
....

But with that sort of code you are probably dealing with a very small set of SQL statements and are specifically wanting different plans etc.

And the champion himself, Tom Kyte, had this reply and defined some new terms: "Overbind" and "Underbind." Using these terms I was asking whether it was possible to "Underbind" using PL/SQL.

The only way to improperly bind in PL/SQL is when you use dynamic sql.

Else, you can "overbind" -- bind when you didn't need to, but you cannot "underbind", not binding when you should!

eg:

for x in ( select * from all_objects where object_type = 'TABLE' )
loop

is perfectly ok, you do NOT need to:

declare
l_otype varchar2(25) := 'TABLE';
begin
for x in ( select * from all_objects where object_type = l_otype )
loop

You do not need to bind 'TABLE' in that query since no matter how many times you run that query, it'll be 'TABLE' over and over.

But with static SQL, it's not possible to "underbind"

As an aside, visit Jeff Hunter's Oracle blog, which is fast becoming one of my favourites. Here is his recent post on where Oracle DBAs can get help:
http://marist89.blogspot.com/2005/06/where-can-i-find-help.html