WinSQL » Knowledge base

Document information

Details

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.

ROWNUM
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:

SELECT  *
FROM   emp
WHERE   ROWNUM  < 11

NOTE:
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.

CURRVAL
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:

schema.sequence_name.CURRVAL

NEXTVAL
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:

schema.sequence_name.NEXTVAL

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

SYSDATE
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

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?

Your Name

Email

Hide my email address

Captcha

Your Tip

Submit Tip