Re: Question about using bind variables in static sql

Let me make this as simple as possible ...

Always always always use bind variables.

If there is an exception it isn't worth worrying about.

Daniel Morgan

Ryan Gaffuri wrote:

> Also what about if you use static sql from with in a pl/sql block?
> Cant remember whether it makes a difference or not.
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<Eb3k8.37775$Yv2.16119_at_rwcrnsc54>...
> > The last 2 are the same (in that the : denotes a host variable) Using host
> > variables is better. You can share the sqlstatements, the execution plans.
> > Good coverage in the application developer guide as to why you should use
> > host variables. It is a quick read.
> > Jim
> > "Ryan Gaffuri" <rkg100_at_erols.com> wrote in message
> > news:6757365f.0203140450.584f22d7_at_posting.google.com...
> > > Sorry wasnt clear...
> > >
> > > Select
> > > From
> > > where dept = &dept
> > > in SQLPLUS
> > >
> > > vs.
> > >
> > > Select
> > > from
> > > where dept = :dept
> > >
> > > vs
> > >
> > > select
> > > from
> > > where dept = v_dept(this is a host variable)
> > >
> > > "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> > news:<%fVj8.34431$af7.28397_at_rwcrnsc53>...
> > > > Not exactly sure what you mean?
> > > > do you mean
> > > > select ... from ... where dept='mydept'
> > > > vs
> > > > select ... from ... where dept=:hvDept
> > > > ?
> > > > If so the 2nd one (has bind variables) is better if another session does
> > the
> > > > same thing
> > > > select ... from ... where dept=:hvDept
> > > > vs
> > > > select ... from ... where dept='hisDept'
> > > > See the documentation for Application development.
> > > > Jim
> > > > "Ryan Gaffuri" <rkg100_at_erols.com> wrote in message
> > > > news:a6p2tl$35a$1_at_bob.news.rcn.net...
> > > > > I know this improves performance in dynamic sql. Does it improve
> > performance
> > > > > in standard sql? When would you want to use a bind variable in static
> > sql?
> > > > > How does the way Oracle handles a bind variable in standard sql differ
> > from
> > > > > how it handles a & variable?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
Received on Thu Mar 14 2002 - 16:44:05 CST