You have already seen a few pseudocolumns in some of the examples in this course. Let us take a closer look at them now.
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:
SYSDATE (current date) and ROWNUM (sequence number of returned row).
The figure shows the query and its results.
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.