Using ROWNUM, SYSDATE, and USER Pseudocolumns With SQL

Previous

Next

A pseudocolumn is similar to a table column, but is not stored in a table. A pseudocolumn returns a value, so it is similar to a function without argument. Oracle Database XE provides several pseudocolumns, such as the ROWNUM, SYSDATE, and USER. The ROWNUM pseudocolumn returns a number indicating the order in which Oracle Database XE selects the row in a query. SYSDATE returns the current date and time set for the operating system on which the database resides. USER returns the name of the user name that is currently logged in.

Example: Using the SQL SYSDATE Pseudocolumn shows the use of the SYSDATE pseudocolumn. Note the use of the DUAL table, which is automatically created by Oracle Database XE for use as a dummy table in SQL statements. See Example: Using SQL Date Functions for another example of the use of SYSDATE.

-- the following statement displays the SYSDATE, which is the current system date
-- NOW is a column alias for display purposes
-- DUAL is a dummy table with one row simply used to complete the SELECT statement
SELECT SYSDATE "NOW" FROM DUAL;

Example: Using the SQL USER Pseudocolumn shows the use of the USER pseudocolumn.

-- display the name of the current user, the user name should be HR
SELECT USER FROM DUAL;

Example: Using the SQL ROWNUM Pseudocolumn shows the use of the ROWNUM pseudocolumn.

-- using ROWNUM < 10 limits the number of rows returned to less than 10
SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10;