Database Reference
In-Depth Information
7.4
Pseudocolumns
Pseudocolumns are virtual columns or expression calculators, the expression
being a constant or another expression. To use a pseudocolumn, you simply
name it in the SQL statement. You can select a pseudocolumn or use it in
an expression or WHERE clause. You cannot insert, update, or delete the
value in a pseudocolumn.
Note:
Contrary to popular belief, values such as SYSDATE, SYSTIMES-
TAMP, USER, and UID are not pseudocolumns but built-in functions.
Table 7.1 lists available pseudocolumns.
Table 7.1
Pseudocolumns in Oracle Database.
Classification
Pseudocolumn
Purpose
ROWID
A relative pointer to a row in the database based
on logical and physical database objects. A concat-
enated set of numbers and letters comprising rela-
tive address pointers to a tablespace, a datafile
block within a tablespace, a row within a block,
and a tablespace datafile number. May also con-
tain a different format if the row is located outside
the database.
ROWNUM
The sequence number of each row retrieved in a
query. Note that ROWNUM is evaluated after a
WHERE clause (before the ORDER BY clause).
The first row is 1, and so on.
Sequences
<sequence>.CURRVAL
Retrieves the current value of a sequence and must
be defined for the session first with NEXTVAL. See
Chapter 22.
Sequences
<sequence>.NEXTVAL
Retrieves the next value of a sequence. Used to
increment a sequence. See Chapter 22.
Hierarchical
LEVEL
Used only in hierarchical queries (using the CON-
NECT BY clause). This returns the level (1, 2, etc.)
of the row. See Chapter 13.
Hierarchical
CONNECT_BY_{IS[LEAF|
CYCLE]}
These pseudocolumns determine if hierarchical
data can be expanded upon. Does an element have
ancestor and/or child entries? More on this in
Chapter 13.
