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 |
|---|
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
Posted by anudeep on 5/17/13 12:51 AM
SYSDATE & ROWNUM is a pseudo column
Posted by Ferrari on 1/6/13 8:53 AM
SYSDATE is a pseudo column.
Posted by somen choudhury on 10/1/10 9:49 AM
SYSDATE is NOT a pseudo column. It's a function in Oracle.
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? Please add it below. Your name and tip will appear at the
end of the document text.
