Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: opinion solicited

Re: opinion solicited

From: Mark Rittman <>
Date: Fri, 09 Jul 2004 19:32:54 +0100
Message-ID: <40eee4ce$0$7803$>

Hi Everyone,

Original poster of the blog article here.

First of all, genuine thanks for the feedback on the article. I'll be summarising the points made and adding them to the article, if they make   the article easier to understand.

Without requoting the article again, the points I was trying to get across were-

  1. Bind variables are important if you want your application to scale beyond a couple of concurrent users.
  2. If you use PL/SQL, it makes all of your program variables bind variables as a matter of course, and therefore in the vast majority of cases (i.e. when you're using anything except dynamic SQL) you are using bind variables by default. I was trying to get this point across, as I know that I (for example) often worried about using bind variables, when in fact PL/SQL takes care of this for you by default (a benefit of using PL/SQL over other languages)
  3. The only time with PL/SQL you really have to explicitely worry about bind variables is if you use dynamic SQL
  4. With Java, VB and so on, if you code using the database access APIs, there is explicit support for the use of bind variables (as bind variables aren't just an Oracle thing, they also are found in SQL Server and so on). You just have to make sure you use these APIs correctly, and not just concatenate SELECT statements together and fire them at the database. As I'm not a Java or VB developer, if in fact this could do with clarifying (i.e. you have to set certain parameters) then I'd be more than happy to amend this statement if someone fills me in on the details.

I think, reading the article back, this is what it says, although I appreciate that other readers might not have found it so clear. Apologies if this is the case, and if anyone has a suggestion as to how to word it better, please let me know and I'll update it with a clarification.


Mark Rittman

Anurag Varma wrote:

> "Ed Stevens" <nospam_at_noway.nohow> wrote in message

>>On Fri, 09 Jul 2004 14:22:26 GMT, "Anurag Varma" <>
>>>"Ed Stevens" <nospam_at_noway.nohow> wrote in message
>>>>Yesterday I made a presentation to a group of our developers. The
>>>>subject was the use of bind variables to reduce parsing and latch
>>>>waits. I presented several demos based on those presented in Tom
>>>>Kyte's book, and provided coding examples for several different
>>>>programmnig environments.
>>>>Later, one of the participants sent me a link to a website whose
>>>>author acknowledged the problems of not using bind variables but
>>>>appeard to be making a case that, in practice, the various coding
>>>>environments (PL/SQL, java, etc.) took care of it for you. He (the
>>>>wesite author) went on to give coding examples where - oh, gee, he was
>>>>coding to use bind variables -- and saying that as long as you coded
>>>>this way, the programming environment would just take care of it for
>>>>you. I thought it was pretty misleading and gave the distinct
>>>>impression to a casual reader that the programmer really didn't need
>>>>to concern himself with the subject.
>>>>Link is at
>>>>I'd be curious to hear other people's take.
>>>hmm .. I don't really agree that the statements in that article/blog are misleading.
>>>He did give an example where even in PL/SQL you can write SQL which will
>>>not utilize bind variables.
>>>For Java/VB etc he says <quote>All of these APIs have built-in support for bind variables,
>>>and it's just a case of using this support rather than just concatenating a string yourself and
>>>submitting it to the database</quote>
>>>Which pretty much states that if you do not use the built in support for bind variables then
>>>you are *not* using bind variables.
>>True enough, but also makes statements like this:
>>"Taking PL/SQL first of all, the good news is that PL/SQL itself takes
>>care of most of the issues to do with bind variables, to the point
>>where most code that you write already uses bind variables without you
>>knowing. "
>>Well. I guess that depends on what "most code that you write "
>>looks like. A lot of programmers write a lot of code by simply
>>replicating bits of (potentially bad) example code they found
>>somewhere. Heck, a lot of DBA's create a lot of databases by simply
>>replicating (potentially bad) examples they found somewhere.
>>I've actually heard a highly-paid consultant/instructor state that (1)
>>Java doesn't support bind variables and (2) therefore, one should
>>always set cursor_shareing=force. Well, to paraphrase a line from The
>>Music Man, "I don't know much about Java, but I do know it supports
>>bind variables." That may seem tangential to the discussion at hand,
>>but I can easily see where some programmers would develop as their
>>standard (and thinking it was a universal standard), practices that do
>>not support bind variables.
>>Please don't think I'm trying to slam you or the blog author. I
>>thought what he wrote was factually correct, but parts were a bit
>>mis-leading, even if unintentional. Thought some commentary might be
>>useful to lurkers, and certainly serves to sharpen my own thinking.
> I guess its just two different opinions. Little knowledge about a subject can be dangerous.
> The article does not go into detail, so just reading that alone might confuse the reader
> rather than enlightening him. However, the links that that blog provides at the end should
> help in explaning the authors point of view in more detail.
> Worse still I recently encountered a vendor which coded its sql statements like this:
> select t1.* from table1 t1 where t1.x = :var;
> select t2.* from table1 t2 where t2.x = :var;
> select t3.* from table1 t3 where t3.x = :var;
> ...
> Its using bind variables ... yoo hooo! ... But it went ahead and assigned different table aliases
> for each sql statement (building sql on the fly and probably using a memory sequence to
> set the table alias).
> .. thus essentially defeating the whole purpose of bind variables ...
> Thus, if one does not understand why bind variables should be used and what the main goal of using
> bind variables is ...
> then there are numerous ways to misinterpret the article.
> Anurag
Received on Fri Jul 09 2004 - 13:32:54 CDT

Original text of this message