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

• SYSDATE
• SYSTIMESTAMP
• ROWID
• ROWNUM
• USER 
• UID
• LEVEL
• CURRVAL
• 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.

Syntax:-

SELECT SYSDATE FROM DUAL

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

Syntax:-

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.

Syntax:-

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.

Code (Text):

SELECT ROWNUM  FROM EMP WHERE ROWNUM <=10


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

Code (Text):

SELECT USER FROM DUAL


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

Code (Text):

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.

Syntax:-

SELECT  level, empno, ename, mgr
FROM     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.

Syntax:-

SELECT <SEQUENCE>.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.

Syntax:-

SELECT <SEQUENCE>.CURRVAL FROM DUAL


With flashback queries, the following Pseudo Columns are available:

versions_xid
versions_operation
versions_startscn
versions_starttime
versions_endscn
versions_endtime