Re: sqlplus question

A copy of this was sent to Kenneth C Stahl <BlueSax_at_Unforgettable.com> (if that email address didn't require changing) On Fri, 15 Oct 1999 08:01:40 -0400, you wrote:

>It is interesting, however, that in the documentation (at least for 7.3.4),
>in the section of the Sql Reference where it describes pseudocolumns 'USER'
>is not listed, nor is SYSDATE. Others such as NEXTVAL, CURVAL, ROWID and
>ROWNUM are. That doesn't make sense.
>

that is because USER is not a psuedo column -- USER is a function. It is documented in with the functions (see OTHER FUNCTIONS for example in the 7.3 doc set). You do not need a database table to have a value for USER. You just need a session.

ROWID is a psuedo column. You *need* a table to have a rowid. A psuedo column relies on a table existing to derive its value. You cannot have a rowid with a table. You can have a USER without a table.

SYSDATE is not a psuedo column either -- you don't need a table to have a DATE.

Here is a fairly generic way to decide if something is a psuedo column:

• if you cannot say

   begin

      x := THAT_THING;
   end;

in plsql -- it is a psuedo column. eg: you cannot say:   

   begin

      x := rownum;
   end;

so rownum is a psuedo column. You can say:

   begin

      x := UID;
   end;

so UID is a function. decode would be one of the exceptions to this rule of thumb as decode is really a function but a function that must be called from SQL (since the sql runtime engine is the place where decode is compiled into and they did not import that function into the plsql engine).

>Sybrand Bakker wrote:
>>
>> Just to avoid any confusion:
>> USER is not a sqlplus variable , it's a SQL pseudo-column.
>> amongst the others are
>> sysdate
>> and userenv
>> the userenv has various options
>> 'sessionid'
>> 'terminal'
>> 'language'
>>
>> Check out the docs and/or technet.oracle.com for more detail
>>
>> Hth,
>>
>> --
>> Sybrand Bakker, Oracle DBA
>> <vk02720_at_my-deja.com> wrote in message news:7u5g4s$87t$1_at_nnrp1.deja.com...
>> > What variables are available globally in a sqlplus session ?
>> > For example :
>> > select USER from dual;
>> > would get the variable USER
>> > What other similar variables can be 'selected from dual' ?
>> >
>> > Thanks.
>> >
>> >
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 15 1999 - 07:20:10 CDT