Select Statement  «Prev  Next»

Lesson 7 Pseudocolumns
Objective Identify the uses of various pseudocolumns.

Oracle Pseudocolumns

Question: What are the various uses of pseudocolumns in Oracle?
Pseudocolumns in Oracle are special columns that behave like regular table columns but are not stored as part of the table. They are used to retrieve specific data or to perform certain operations. Here are some common uses of pseudocolumns in Oracle:
  1. ROWNUM: ROWNUM is a pseudocolumn that assigns a unique number to each row in the result set of a query. It is often used for limiting the number of rows returned or for pagination.
    Example: 
    SELECT * FROM employees
WHERE ROWNUM <= 10;

    This query returns the first 10 rows from the employees table.
  2. ROWID: ROWID is a pseudocolumn that represents the unique address of a row in the database. It can be used to quickly access a specific row or to identify and remove duplicate rows.
    Example: 
    DELETE FROM employees
WHERE ROWID IN (
  SELECT ROWID
  FROM (
    SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY ROWID) AS row_num
    FROM employees
  )
  WHERE row_num > 1
);

    This query deletes duplicate rows from the employees table based on first_name and last_name columns.
  3. LEVEL: LEVEL is a pseudocolumn used with hierarchical queries to indicate the level of a row in a hierarchical tree structure. It is used in conjunction with the CONNECT BY clause to retrieve hierarchical data.
    Example: 
    SELECT LEVEL, employee_id, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

    This query retrieves the hierarchical structure of employees and their managers.
  4. NEXTVAL and CURRVAL: NEXTVAL and CURRVAL are pseudocolumns associated with sequences in Oracle. NEXTVAL is used to generate the next value in a sequence, while CURRVAL retrieves the current value of a sequence.
    Example: 
    INSERT INTO employees (employee_id, first_name, last_name)
VALUES (employee_seq.NEXTVAL, 'Glenn', 'Gould');

SELECT employee_seq.CURRVAL FROM DUAL;

    The first query inserts a new employee with the next value from the employee_seq sequence, and the second query retrieves the current value of the sequence.
  5. SYS_GUID(): SYS_GUID() is a pseudocolumn that generates a unique identifier (GUID) value. It is useful when you need a unique value across tables or databases.
    Example: 
    INSERT INTO employees (employee_id, first_name, last_name)
VALUES (SYS_GUID(), 'John', 'Doe');

    This query inserts a new employee with a unique identifier generated by SYS_GUID().
These are just a few examples of the various uses of pseudocolumns in Oracle. They can provide valuable functionality and help simplify complex operations within your database queries.

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.

ROWNUM pseudocolumn appears here in the SELECT clause just like any column
  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