Pseudo Columns in Oracle

 A pseudocolumn behaves like a table column, but it's not actually stored in the table.

TYPES::

1.SYSDATE
2.SYSTIMESTAMP
3.USER
4.UID
5.ROWNUM
6.ROWID
7.NEXTVAL    --- Sequence Related Pseudo Columns
8.CURVAL --  Sequence Related Pseudo Columns


1.SYSDATE::

SYSDATE returns the current date set for the operating system on which the database resides. The datatype of the returned value is DATE.

Example::

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
---------
10-JUN-16

2.SYSTIMESTAMP::

SYSTIMESTAMP is the built in function which returns the current database system date including fractional seconds and region time zone.

Example::

SQL> SELECT SYSTIMESTAMP FROM Dual;

SYSTIMESTAMP
-----------------------------------
10-JUN-16 07.20.13.909000 PM +05:30

3.USER::

The USER indicate in Which USER We are Currently loged In.

Example::

SQL> SELECT USER FROM Dual;

USER
-----
HR

4.UID::

The Oracle UID is a pseudo-column containing a numeric value identifying the current user. Calling the UID() function will return the ID number, the user who is currently logged in.

Example::

SQL> SELECT UID FROM Dual;

         UID
----------
         33

SQL> SELECT USER,UID FROM Dual;

USER                                  UID
------------------------------ ----------
HR                                     33

5.ROWNUM::

ROWNUM is a pseudocolumn returning a sequential number along with the rows retrieved.

Example::

SQL> SELECT ROWNUM "S.NO",Department_Id,Department_Name From Departments;

------------------------------------------------------------------------
      S.NO     DEPARTMENT_ID         DEPARTMENT_NAME
---------- ------------- -----------------------------------------------
         1            10                                Administration
         2            20                                Marketing
         3            30                                 Purchasing
         4            40                                 Human Resources
         5            50                                 Shipping
         6            60                                 IT
         7            70                                 Public Relations
         8            80                                 Sales
         9            90                                 Executive
        10           100                               Finance
        11           110                               Accounting
        12           120                               Treasury
        13           130                               Corporate Tax
        14           140                               Control And Credit
        15           150                               Shareholder Services

Example::

To display &n(th ) Maximum Salary 
=======================================================
SELECT MIN(SALARY)
FROM
   (
    SELECT ROWNUM,SALARY FROM
             (
SELECT SALARY FROM EMPLOYEES ORDER BY 1 DESC)
 WHERE ROWNUM <= nth
    );
=======================================================

6.ROWID::

An Oracle server assigns each row in each table with a unique ROWID(Address ID) to identify the row in the table.

Example::

SQL> SELECT ROWID,Department_Name From Departments;
------------------                            -----------------------------
ROWID                                      DEPARTMENT_NAME
------------------                          -----------------------------
AAAC8/AAEAAAAA3AAA         Administration
AAAC8/AAEAAAAA3AAB        Marketing
AAAC8/AAEAAAAA3AAC        Purchasing
AAAC8/AAEAAAAA3AAD        Human Resources
AAAC8/AAEAAAAA3AAE        Shipping
AAAC8/AAEAAAAA3AAF        IT
AAAC8/AAEAAAAA3AAG        Public Relations