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


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 somen choudhury on 10/1/10 9:49 AM

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

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.


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.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com