Select Statement  «Prev  Next»

Lesson 7 Pseudocolumns
Objective Identify the uses of various pseudocolumns.

Oracle Pseudocolumns

Oracle Exclusive

You have already seen a few pseudocolumns in some of the examples in this course. Let us take a closer look at them now.

Pseudocolumns

A pseudocolumn acts like a column in your query but is actually a value assigned by the Oracle server when the query is executed. Pseudocolumns are a convenient method of retrieving information about your query, your environment, and your data. In some cases, the pseudocolumn is assigned a different value for each row of the query (see the ROWNUM and ROWID pseudocolumns). In other cases, the pseudocolumn is assigned a constant value for the entire query (see the USER and SYSDATE pseudocolumns). You can use pseudocolumns anywhere in a query that you use a column. You cannot, however, update a pseudocolumn, and there is no actual column in your table that corresponds to the pseudocolumn you use in your query.
Here is an example of a query that uses two pseudocolumns:
  1. SYSDATE (current date) and
  2. ROWNUM (sequence number of returned row).
The figure shows the query and its results.

  1. The ROWNUM pseudocolumn appears here in the SELECT clause just like any column
  2. The ROWNUM pseudocolumn appears here in the SELECT clause just like any column.
  3. The SYSDATE pseudocolumn is concatenated to a literal in the SELECT clause. The SYSDATE pseudocolumn contains the current date and time. Its default display format is the same as other dates.
  4. The ROWNUM values appear here and are incremented for each row.
  5. The SYSDATE values appear here and are the same for each row returned.

Query using Two Pseudocolumns

Oracle’s pseudocolumns

The following table shows a list of pseudocolumns that Oracle provides.
Pseudocolumn name Description
LEVEL Level in a hierarchy (such as 1,2,...
NULL Null value
ROWID Value representing the internal row ID of a table row
ROWNUM Sequential number of record (1,2,3, ...)
SYSDATE Current date and time
USER Current Oracle user
UID Value representing the internal row ID for an object row
sequence.CURRVAL The current value in a sequence
sequence.NEXTVAL The next value in a sequence

Pseudocolumns are especially useful when inserting or updating rows. For example, the pet store schema contains the PET_CARE_LOG table in which you record the date, time, and the user that inserts a new comment. You could write an INSERT command in SQL that uses the USER and SYSDATE pseudocolumns to assign the values for those two columns in the PET_CARE_LOG.

Oracle Pseudocolumns - Quiz

The next lesson concludes this module. Click the Quiz link below to take a quiz on pseudocolumns.
Oracle Pseudocolumns - Quiz