Re: SELECT ... FROM DUAL ?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sat, 18 Sep 1999 09:52:10 +0800
Message-ID: <37E2F04A.4100@yahoo.com>

Frank Hubeny wrote:
>
> I was impressed by the thoroughness of the analysis which Thomas Kyte provided about
> the use of sysdate and user -- especially with the actual display of tkprof output
> showing that a reference to sysdate in a plsql program was the same as a select from
> dual.
>
> A slight variation on his method of avoiding multiple calls to sysdate by using a
> global constant would be to use a global variable returned via a function call as
> follows:
>
> create or replace package body my_globals
> as
> g_user date;
>
> function getdate return date
> is
> begin
> if g_user is null then
> g_user := sysdate;
> end if;
> return g_user;
> end;
> end;
>
> This way the call to sysdate is made only if required and is only made once.
>
> For web applications where the user's connection is brief, this might be a desirable
> way to handle calls to sysdate.
>
> Frank Hubeny
>
> Thomas Kyte wrote:
>
> > create or replace package my_globals
> > as
> > g_user constant varchar2(30) default USER;
> > g_date constant date default SYSDATE;
> > g_sessionid constant number default userenv( 'sessionid' );
> > end;
> > /
> >
> > and then refer to my_globals.g_user, my_globals.g_date, etc in your code as they
> > are plsql variables and won't go back to the kernel for values each time they
> > are referenced.
> >
> > the *downside* to this approach is that if your procedure only needs the USER
> > psuedo column -- it'll get the date and sessionid from the database anyway (you
> > either get ALL of them or none of them in a session). In a large program that
> > uses all of them, this won't matter. In a small program, since its done but
> > once -- its probably not even noticable.
> >

True - but once the package is loaded, sysdate wouldn't change so (as you state) its only going to be useful for short connections. --

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Fri Sep 17 1999 - 20:52:10 CDT