Re: SELECT from DUAL??

Joseph Ranseth wrote:
>
> Okay, that makes sense....but in cases that I've seen functions used - I'm
> assuming that there is no benefit whatsoever?
>
> Thanks.
>
> <steveee_ca_at_my-deja.com> wrote in message
> news:945agl$s3v$1_at_nnrp1.deja.com...
> > Hi,
> >
> > The direct assignment syntax won't work if the assignment you're trying
> > to make is not a function. For instance, the nextval of a sequence
> > called my_seq couldn't be assigned with:
> >
> > v_next := my_seq.nextval;
> >
> > You need to :
> >
> > SELECT MY_SEQ.NEXTVAL
> > INTO v_next FROM DUAL;
> >
> > Hth,
> >
> > Steve
> >
> >
> >
> >
> > In article <wtp96.790$wb7.15596_at_news1.mts.net>,
> > "Joseph Ranseth" <jransethNO_SPAM_at_hotmail.com> wrote:
> > > I have come across many instances where people use a select from dual
 to
> > > determine or assign a value to a local variable, for example:
> > >
> > > SELECT sysdateinto l_date FROM dual
> > >
> > > what is the benefit of this over using a standard assignment
 expression?
> > > ie: l_date := sysdate;
> > >
> > > Just wondering....
> > > thanks in advance.
> > >
> > > JR
> > >
> > >
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/

As much as possible you want to use the assignment in PL/SQL since using select will invoke the SQL engine, the jumping between the two is a performance overhead.

To confuse things, some assignments will implicitly invoke an SQL anyway.

For example,

turn a trace on

x := sysdate

and typically you'll see a recursive "select sysdate from dual"

HTH
COnnor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"