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.

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:

1. SYSDATE (current date) and
2. ROWNUM (sequence number of returned row).

The diagram below shows the query and its results.

1. The ROWNUM pseudocolumn appears here in the SELECT clause just like any column.
2. 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.
3. The ROWNUM values appear here and are incremented for each row.
4. The SYSDATE values appear here and are the same for each row returned.

Question: How do I execute a query using two Pseudocolumns in Oracle?
In Oracle, a pseudocolumn is a column-like object that behaves like a regular column in a table, but it is not stored as a part of the table. Pseudocolumns can be used in SQL queries to retrieve data, perform calculations, or generate unique values. Two common pseudocolumns in Oracle are ROWNUM and ROWID.
To execute a query using two pseudocolumns in Oracle, you can follow the steps below:

1. Identify the pseudocolumns you want to use in your query. In this example, we will use ROWNUM and ROWID.
2. Write the SELECT statement, specifying the columns you want to retrieve from the table, along with the pseudocolumns.
3. Include the pseudocolumns in the SELECT clause, either by using them directly or by applying functions or expressions to them.
4. Use the appropriate clauses (e.g., WHERE, GROUP BY, ORDER BY) to filter, group, or sort the result set based on the pseudocolumn values.

Here's an example of a query using the ROWNUM and ROWID pseudocolumns to retrieve data from the "employees" table:

SELECT ROWNUM AS row_number, ROWID AS row_identifier, first_name, last_name, salary
FROM employees
WHERE ROWNUM <= 10
ORDER BY salary DESC;

In this query:

1. The ROWNUM pseudocolumn is used to assign a unique row number to each row in the result set.
2. The ROWID pseudocolumn is used to retrieve the unique row identifier for each row in the table.
3. The WHERE clause filters the result set to include only the first 10 rows, based on the ROWNUM pseudocolumn value.
4. The ORDER BY clause sorts the result set in descending order of the "salary" column.

Keep in mind that using pseudocolumns can sometimes lead to unexpected results, particularly when combining them with other clauses. Make sure to test your queries thoroughly to ensure they produce the desired outcome.

Many columns must have values that are within a certain range or that satisfy certain conditions. With a CHECK constraint, you can specify an expression that must always be true for every row in the table. For example, the RATING table stores the valid ratings; to limit the available values beyond the limits enforced by the column definition, you can use a CHECK constraint, as shown in the following listing:

create table RATING_WITH_CHECK
(Rating VARCHAR2(2) CHECK (Rating <= 9),
RatingDescription VARCHAR2(50));

A column-level CHECK constraint can’t refer to values in other rows; it can’t use the pseudocolumns such as SysDate, User, CurrVal, NextVal, Level, and RowNum. You can use the table constraint form (as opposed to the column constraint form) to refer to multiple columns in a CHECK constraint.

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.

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