Oracle SQL | Pseudocolumn

In Oracle, a pseudocolumn behaves like a table column but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. These pseudocolumns give you access to specific data or metadata that is inherent to the table or database itself.

Here are some of the most commonly used pseudocolumns in Oracle:

ROWID: Every row in the database has an address or unique identifier, known as its ROWID. The ROWID pseudocolumn returns the address of each row. It's unique for every row in the database and can be useful for row-level operations.
ROWNUM: For each row returned by a query, the ROWNUM pseudocolumn returns a number that indicates the order in which Oracle selects the row from a table or set of joined rows.
```
SELECT ROWNUM, column_name FROM table_name WHERE ROWNUM <= 10;
```
This example would fetch the first 10 rows from a table.
ORA_ROWSCN: Returns the conservative upper bound system change number (SCN) of the most recent change to the row. It can be useful to determine if a row has changed since it was last queried.
NEXTVAL and CURRVAL (for sequences):
- NEXTVAL: Returns the next value in a sequence. When you reference a sequence's NEXTVAL, the sequence is incremented immediately.
- CURRVAL: Returns the current value of a sequence. It's useful to retrieve the most recently fetched value from the sequence.
```
SELECT sequence_name.NEXTVAL FROM DUAL;
SELECT sequence_name.CURRVAL FROM DUAL;
```
LEVEL: Used in hierarchical queries. It returns the level number of a node in a tree structure. Typically used with the START WITH and CONNECT BY clauses.
SYS_GUID(): Not a pseudocolumn in the traditional sense, but this function returns a globally unique identifier suitable for generating unique primary key values.
UID and USER:
- UID: Returns the user ID of the current Oracle session.
- USER: Returns the name of the current Oracle user.

Example:

SELECT UID, USER FROM DUAL;

To use pseudocolumns, you often reference them directly in your queries. Keep in mind that while they behave much like columns, they're not stored in the table and are often generated on-the-fly when queried.

Examples of Oracle SQL ROWID pseudocolumn:
- The ROWID pseudocolumn uniquely identifies a row in a table.
```
SELECT ROWID, column1, column2
FROM your_table;
```
Using ROWNUM pseudocolumn in Oracle SQL:
- The ROWNUM pseudocolumn assigns a unique number to each row returned by a query.
```
SELECT ROWNUM, column1, column2
FROM your_table;
```
Oracle SQL pseudocolumns vs. regular columns:
- Pseudocolumns have specific behaviors and purposes, like ROWID and ROWNUM, whereas regular columns store data in tables.
```
-- Regular column
SELECT employee_id, first_name, last_name
FROM employees;

-- Pseudocolumn
SELECT ROWID, column1, column2
FROM your_table;
```
ROWID vs. ROWNUM in Oracle SQL:
- ROWID uniquely identifies a row, while ROWNUM assigns a unique number to each row in the result set.
```
SELECT ROWID, column1, column2
FROM your_table;

SELECT ROWNUM, column1, column2
FROM your_table;
```
Oracle SQL LEVEL pseudocolumn in hierarchical queries:
- The LEVEL pseudocolumn is used in hierarchical queries to represent the level in a tree structure.
```
SELECT employee_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
```
How to use PRIOR pseudocolumn in CONNECT BY queries:
- The PRIOR pseudocolumn refers to the parent row in hierarchical queries using CONNECT BY.
```
SELECT employee_id, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
```
Oracle SQL pseudocolumns in SELECT statement:
- Pseudocolumns can be used in the SELECT statement to retrieve specific information about rows.
```
SELECT ROWID, ROWNUM, employee_id, salary
FROM employees;
```
Oracle SQL pseudocolumns in WHERE clause:
- Pseudocolumns can be used in the WHERE clause to filter rows based on specific conditions.
```
SELECT employee_id, salary
FROM employees
WHERE ROWNUM <= 10;
```
Oracle SQL pseudocolumns for versioning:
- Pseudocolumns like ORA_ROWSCN can be used for versioning to track changes in a row.
```
SELECT employee_id, salary, ORA_ROWSCN
FROM employees;
```
Pseudocolumns and triggers in Oracle SQL:
- Pseudocolumns can be used in combination with triggers to capture information about changes in a table.
```
CREATE OR REPLACE TRIGGER your_trigger
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    :NEW.rowid_column := ROWID;
END;
/
```
Oracle SQL pseudocolumns for flashback queries:
- Pseudocolumns like VERSIONS_STARTSCN and VERSIONS_ENDSCN can be used in flashback queries to retrieve historical data.
```
SELECT employee_id, salary, VERSIONS_STARTSCN, VERSIONS_ENDSCN
FROM employees
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
```
Applying Oracle SQL pseudocolumns in subqueries:
- Pseudocolumns can be used in subqueries to filter or join data based on specific conditions.
```
SELECT employee_id, salary
FROM employees
WHERE ROWID IN (SELECT ROWID FROM another_table WHERE condition);
```