|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:
WHERE ROWNUM < 11
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.
Posted by somen choudhury on 10/1/10 9:49 AM
Posted by Ferrari on 1/6/13 8:53 AM
Posted by anudeep on 5/17/13 12:51 AM
Do you have a helpful tip related to this document that you'd like to share with other users?