WinSQL » Knowledge base

Document information

Document ID: 772
Subject: What are Pseudo-columns in Oracle
Creation date: 7/29/09 3:02 PM
Last modified on: 7/29/09 3:10 PM


Oracle supports several special-purpose data elements that are not actually contained in a table, but are available for use in SQL statements as if they were part of the table.

Here is a partial list of pseudo-columns in Oracle. For a complete list refer to Oracle's reference guide.

For each row of data returned by a SQL query, ROWNUM will contain a number indicating the order in which the row was retrieved. For example, the first row retrieved will have a ROWNUM of 1, the second row will have a ROWNUM of 2, and so on. This approach can be useful for limiting the number of rows returned by a query. To display only ten rows of the emp table, the following SQL statement makes use of the ROWNUM pseudo-column:

FROM   emp

ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. For example, if a SQL statement includes an ORDER BY clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is assigned before the sort operation.

When using Oracle SEQUENCE values, the pseudo-column CURRVAL returns the current value of the sequence. To reference CURRVAL, it must be associated with a sequence, for example:


When using Oracle SEQUENCE values, the pseudo-column NEXTVAL returns the next value of the sequence and causes the sequence to increment by one. You can only reference NEXTVAL if it is associated with a sequence. For example:


This pseudo-column will always contain the Oracle username under which you are connected to the database.

This pseudo-column will contain the current date and time. This column is a standard Oracle DATE datatype. The value represents the current time on the server not the client.

User comments

Posted by somen choudhury on 10/1/10 9:49 AM

SYSDATE is NOT a pseudo column. It's a function in Oracle.

Posted by Ferrari on 1/6/13 8:53 AM

SYSDATE is a pseudo column.

Posted by anudeep on 5/17/13 12:51 AM

SYSDATE & ROWNUM is a pseudo column

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users?

Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our public forums to post questions.


Social Media

Powered by