Tuesday, June 14, 2005

Bind Variables in PL/SQL

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

Comments:
Hi Robert,
All the links you provided for references are not working.. Every link returns the errorsome page.
Can you please help us by updating them..
Regards,
Dipali..
 
This comment has been removed by a blog administrator.
 
Hello. Your reference links are out-of-date. None of them work.
 
Yes, this post is 5 years old and some of those sites have changed.

I've updated the links, so they should all work, except for the Dizwell Forum, which is no longer available (and wasn't very reliable when it was, as it was subject to the whimsy of its brilliant but unbalanced owner).
 
Hello sir,

I am new to pl/sql and learning cursor.

what i understand about cursor and ref cursor is :

cursor is a pointer or object that points to fixed location in context area.

ref cursor is a a variable that points to a pointer or object that
points to fixed location in context area.

So,normal cursor is static as they points to a particular location in context area whereas ref cursor is not as they points to different locations.

Now,there is a code:

CREATE OR REPLACE PROCEDURE authors_sel (
cv_results IN OUT SYS_REFCURSOR)
IS
BEGIN
OPEN cv_results FOR
SELECT id, first_name, last_name
FROM authors;
END;
/

And we are calling this by:

VARIABLE x REFCURSOR
EXEC authors_sel(:x)
PRINT x

Why we are using bind variables here?

Is there any alternative?

Please help me.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?