Pseudo Columns in Oracle

Pseudo columns are associated with table data , but nothing to do with table data and retrieved from database.
 But it looks like retrieved from the table data directly .

Some of the Most commonly used Pseudo columns in Oracle are

1. SYSDATE
2. SYSTIMESTAMP
3. ROWID
4. ROWNUM
5. USER
6. UID
7. LEVEL
8. CURRVAL
9. NEXTVAL

SYSDATE
-------

It shows the Current date from the local or remore database .
We can use the CURRENT_DATE also with for the same purpose.

SQl> SELECT SYSdate  FROM DUAL

SYSTIMESTAMP
------------
Systimestamp function returns the current system date and time
(including fractional seconds and time zone) on your database

SQl> SELECT SYSTIMESTAMP  FROM DUAL

ROWID
-----

Rowid is a pseudo column that uniquely identifies a row within a table,
but not within a database. It is possible for two rows of two different tables stored
 in the same cluster to have the same rowid

SQl> SELECT ROWID FROM EMP

ROWNUM
-------
Rownum numbers the records in a result set. The first record that meets the where criteria
in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.

SQl>SELECT ROWNUM  FROM EMP WHERE ROWNUM =1;

SQl>SELECT ROWNUM  FROM EMP WHERE ROWNUM <=1;

SQl>SELECT ROWNUM  FROM EMP WHERE ROWNUM >=10; --o/p: no rows returned
SQl>SELECT ROWNUM  FROM EMP WHERE ROWNUM >10; --o/p: no rows returned

Rownum does not work with > or >= ineuqlities.

USER
----
User is a pseudo column that returns the name of the user currently connected to the session.

SQl> SELECT USER FROM DUAL;--o/p :SCOTT

UID
----
Uid is a pseudo column that returns the id number of a user currently connected to the session.

sql>SELECT UID FROM DUAL;


LEVEL
-----

LEVEL pseudo-column is an indication of how deep in the tree one is.
It is used in hierarchical queries along with CONNECT by clause.

sql> SELECT  level, empno, ename, mgr
     FROM     scott.emp
     CONNECT BY PRIOR empno = mgr
     START WITH mgr IS NULL;


NEXTVAL
------

NEXTVAL is used to invoke a sequence. If nextval is invoked on a sequence,
it makes sure that a unique number is generated

SQL> SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;


CURRVAL
-------
CURRVAL can only be used if a session has already called nextval on a trigger.
currval will then return the same number that was generated with nextval.

SQL> SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;

Ur's

AmarAlam