Pseudo Columns - ( SYSDATE, SYSTIMESTAMP, USER, UID, ROWNUM, ROWID, NEXTVAL, CURRVAL,LEVEL)

1) SYSDATE

This command is used to display the current system date in DD-MOM-YY

Example

SELECT SYSDATE FROM DUAL;

SYSDATE
---------
01-FEB-19

2) SYSTIMESTAMP

This command is used to display the current system date & time.

Example

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
01-FEB-19 12.40.48.603000 AM +05:30

3) USER

This command shows you the current user you have logged in.

Example

SELECT USER FROM DUAL;

USER
------------------------------
HR

4) UID

This command shows you the current user ID you have logged in.

Example

SELECT UID FROM DUAL;

      UID
----------
        43

5) ROWNUM

This command is used to generate a row number starting from 1 and will not be stored in the database.

Example

SELECT ROWNUM FROM EMPLOYEES;

6) ROWID

ROWID is unique and generated when the row is inserted. It has 18 digit characters (contains mixed or character & numbers). This command is USER to display row id.

Example

SELECT ROWID FROM EMP;

ROWID
------------------
AAAE5jAAEAAAAF8AAA
AAAE5jAAEAAAAF8AAB
AAAE5jAAEAAAAF8AAC
AAAE5jAAEAAAAF9AAA
AAAE5jAAEAAAAF+AAB

7) NEXTVAL

This command is used to show the next available value from a sequence which can be used.

Example

SELECT SEQ10.NEXTVAL FROM DUAL;

8) CURRVAL

This command shows the current value from the sequence that has been already used.

Example

SELECT SEQ10.CURRVAL FROM DUAL;

9) LEVEL

Example

SELECT
EMPLOYEE_ID, FIRST_NAME, MANAGER_ID,
LEVEL
AS
STAGE
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR
EMPLOYEE_ID=MANAGER_ID
ORDER BY STAGE ASC