Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: opinion solicited

From: Mark Rittman <mark_at_rittman.net>
Date: Sat, 10 Jul 2004 08:38:42 +0100
Message-ID: <40ef9cfa$0$7792$db0fefd9@news.zen.co.uk>


Ed,

General point taken; however, the only context in which I said things are 'taken care of for you' is PL/SQL, which I'm sure most people would agree does 'take care of' bind variables for you unless you really go out of your way not to use them, i.e. use dynamic SQL. The particular paragraph in the article is

"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 ... Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable - this is just the way that PL/SQL works ... In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using the tactical nuclear option of PL/SQL, 'Dynamic SQL' ... So for PL/SQL, things are actually quite straightforward. Normal PL/SQL variables are infact bind variables, and literal values 'hard coded' into the package code are static anyway, and won't benefit from turning into bind variables. The only time you need to specifically use bind variables is when you're putting together dynamic SQL."

When it comes to VB and Java, I'm more cautious:

"The next question I had, though, was what about VB, Java and other applications that fire SQL queries against an Oracle database. How do these use bind variables? Do you have to in fact split your SQL into two statements, one to set the bind variable, and one for the statement itself?

In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. 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.

For example, Java has PreparedStatement, which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature."

Ed, I take your point though about it could be confusing, and what I guess we're actually discussing is more the emphasis than anything else. As you suggest, I'll reword the relevant part to make it a bit clearer.

Thanks again for the feedback

kind regards

Mark

"

Ed Stevens wrote:

> Mark,
> 
> I think if I were editing the article, I'd eliminate statements that
> say or imply that the programming environment (whatever it is) "takes
> care of it for you."  It would be more accurate to say that the
> programming environment "supports the use . . . .".  (And I'm not
> aware of any that don't).  The Big "IF" (tm) is "IF you write your
> code properly"  and don't assume they are writing it properly.
> Imagine a freshly-minted programmer who thinks the world revolves
> around Java and databases are an evil imposed on him by
> 'unenlightened' dinasaurs - "I can do it all in my java code, thank
> you very much . . . ".
> 
> 
> On Fri, 09 Jul 2004 19:32:54 +0100, Mark Rittman <mark_at_rittman.net>
> wrote:
> 
> 

>>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.
>>
>>regards
>>
>>Mark Rittman
>>
>>
>>Anurag Varma wrote:
>>
>>>"Ed Stevens" <nospam_at_noway.nohow> wrote in message
>>>news:2dete0ploue440mleko24bd1cc6skaqf38_at_4ax.com...
>>>
>>>
>>>>On Fri, 09 Jul 2004 14:22:26 GMT, "Anurag Varma" <avdbi_at_hotmail.com>
>>>>wrote:
>>>>
>>>>
>>>>
>>>>>"Ed Stevens" <nospam_at_noway.nohow> wrote in message
>>>>>news:me3te0h1o11p1m7pmqarlv0kk6a45j841v_at_4ax.com...
>>>>>
>>>>>
>>>>>>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 http://www.rittman.net/archives/000832.html
>>>>>>
>>>>>>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.
>>>>>
>>>>>
>>>>>
>>>>>Anurag
>>>>>
>>>>
>>>>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 Sat Jul 10 2004 - 02:38:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US