Named pseudocolumns as they are not actually part of the table, so not actually stored in the table.
Data of these columns can only be selected, but cannot be inserted, updated or deleted.
Some of the pseudocolumns available in Oracle are:
1. Hierarchical Query Pseudocolumn :
Used only in hierarchical queries. Examples -
CONNECT_BY_ISCYCLE - returns 1 if the parent row is also the child row of the current row, otherwise it returns 0. This is used specifically for checking the following error
ERROR:
ORA-01436: CONNECT BY loop in user data
But it can only be used in the case when the CONNECT BY clause has used the NOCYCLE parameter.
Example reference for the cycle
CONNECT_BY_ISLEAF - returns 1 if the current row is the leaf of the tree defined in the CONNECT BY condition. Otherwise it returns 0.
LEVEL - returns data based on the level of the current row in the inverted tree. 1 for the root row, 2 for its child, 3 for the next child in the tree and so on.
2. Sequence Pseudocolumns :
Sequence is a schema object which can generate unique sequential values. These values can be used for primary key of the table.
The CURRVAL and NEXTVAL are the pseudocolumns that are used for the sequence.
3. Version Query Pseudocolumns :
Applicable only in the flashback queries, specifically flashback version queries. The pseudocolumns are:
Used when a virtual table is created using the TABLE function.
5. OBJECT_ID pseudocolumn :
returns the object identifier of the column in object table or view. Used mostly when INSTEAD OF trigger is used for any view.
6. OBJECT_VALUE pseudocolumn :
returns the system generated names of the columns used in object table.
7. ROWID pseudocolumn :
contains the address of each row of the table. It is the fastest way to access a row in the database.
Usually each row in the database has a unique rowid value. However, the rows of different tables stored together in the same cluster can have same rowids.
It contains the following information required to locate a row:
a> data object number of the object
b> the datafile of the tablsespace in which the row resides
c> datablock in the datafile where the row is stored
d> inside that datablock, the position of the row. The first row is termed as position 0.
8. ROWNUM pseudocolumn :
ROW_NUMBER build-in SQL function provides a unique way to order the resultset. The ROWNUM column is used to order the resultset of a query. Each row is assigned a unique number in the order(1,2,3,4,5...)
If order by clause is used in the same query where rownum is used, the order by will force the results to be reordered. But if the order by is used in a sub-query and rownum is used in the top level query, the rownum will be used on the ordered data.
References:
Main Article
Data of these columns can only be selected, but cannot be inserted, updated or deleted.
Some of the pseudocolumns available in Oracle are:
- Hierarchical Query Pseudocolumns
- Sequence Pseudocolumns
- Version Query Pseudocolumns
- COLUMN_VALUE Pseudocolumn
- OBJECT_ID Pseudocolumn
- OBJECT_VALUE Pseudocolumn
- ROWID Pseudocolumn
- ROWNUM Pseudocolumn
1. Hierarchical Query Pseudocolumn :
Used only in hierarchical queries. Examples -
CONNECT_BY_ISCYCLE - returns 1 if the parent row is also the child row of the current row, otherwise it returns 0. This is used specifically for checking the following error
ERROR:
ORA-01436: CONNECT BY loop in user data
But it can only be used in the case when the CONNECT BY clause has used the NOCYCLE parameter.
Example reference for the cycle
CONNECT_BY_ISLEAF - returns 1 if the current row is the leaf of the tree defined in the CONNECT BY condition. Otherwise it returns 0.
LEVEL - returns data based on the level of the current row in the inverted tree. 1 for the root row, 2 for its child, 3 for the next child in the tree and so on.
2. Sequence Pseudocolumns :
Sequence is a schema object which can generate unique sequential values. These values can be used for primary key of the table.
The CURRVAL and NEXTVAL are the pseudocolumns that are used for the sequence.
3. Version Query Pseudocolumns :
Applicable only in the flashback queries, specifically flashback version queries. The pseudocolumns are:
- VERSIONS_STARTTIME: Returns the timestamp of the first version of the rows returned by the query.
- VERSIONS_STARTSCN: Returns the SCN of the first version of the rows returned by the query.
- VERSIONS_ENDTIME: Returns the timestamp of the last version of the rows returned by the query.
- VERSIONS_ENDSCN: Returns the SCN of the last version of the rows returned by the query.
- VERSIONS_XID: For each version of each row, returns the transaction ID (a RAW number) of the transaction that created that row version.
- VERSIONS_OPERATION: For each version of each row, returns a single character representing the operation that caused that row version. The values returned are I (for an insert operation), U (for an update operation) or D (for a delete operation).
Used when a virtual table is created using the TABLE function.
5. OBJECT_ID pseudocolumn :
returns the object identifier of the column in object table or view. Used mostly when INSTEAD OF trigger is used for any view.
6. OBJECT_VALUE pseudocolumn :
returns the system generated names of the columns used in object table.
7. ROWID pseudocolumn :
contains the address of each row of the table. It is the fastest way to access a row in the database.
Usually each row in the database has a unique rowid value. However, the rows of different tables stored together in the same cluster can have same rowids.
It contains the following information required to locate a row:
a> data object number of the object
b> the datafile of the tablsespace in which the row resides
c> datablock in the datafile where the row is stored
d> inside that datablock, the position of the row. The first row is termed as position 0.
8. ROWNUM pseudocolumn :
ROW_NUMBER build-in SQL function provides a unique way to order the resultset. The ROWNUM column is used to order the resultset of a query. Each row is assigned a unique number in the order(1,2,3,4,5...)
If order by clause is used in the same query where rownum is used, the order by will force the results to be reordered. But if the order by is used in a sub-query and rownum is used in the top level query, the rownum will be used on the ordered data.
References:
Main Article
