In this article, we will learn PseudoColumns 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.

A pseudocolumn is also similar to a function without arguments However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

We will discuss on below PseudoColumns in Oracle.

• Hierarchical Query Pseudocolumns
• Sequence Pseudocolumns
• Version Query Pseudocolumns
• COLUMN_VALUE Pseudocolumn
• OBJECT_ID Pseudocolumn
• OBJECT_VALUE Pseudocolumn
• ROWID Pseudocolumn
• ROWNUM Pseudocolumn

Hierarchical Query Pseudocolumns:

The hierarchical query pseudocolumns are valid only in hierarchical queries. The hierarchical query pseudocolumns are:

• CONNECT_BY_ISCYCLE Pseudocolumn
• CONNECT_BY_ISLEAF Pseudocolumn
• LEVEL Pseudocolumn

To define a hierarchical relationship in a query, you must use the CONNECT BY clauses.

CONNECT_BY_ISCYCLE Pseudocolumn:

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.

You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause.

NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

CONNECT_BY_ISLEAF Pseudocolumn:

The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0.

LEVEL Pseudocolumn:

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children.

 

Sequence Pseudocolumns:

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL: Returns the current value of a sequence

NEXTVAL: Increments the sequence and returns the next value

You must qualify CURRVAL and NEXTVAL with the name of the sequence:

sequence.CURRVAL
sequence.NEXTVAL

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

A sequence can be accessed by many users concurrently with no waiting or locking.

Where to use Sequence Values:

You can use CURRVAL and NEXTVAL in the following locations:

• The select list of a SELECT statement that is not contained in a subquery, materialized view, or view
• The select list of a subquery in an INSERT statement
• The VALUES clause of an INSERT statement
• The SET clause of an UPDATE statement

Restrictions on Sequence Values: 
You cannot use CURRVAL and NEXTVAL in the following constructs:

• A subquery in a DELETE, SELECT, or UPDATE statement
• A query of a view or of a materialized view
• A SELECT statement with the DISTINCT operator
• A SELECT statement with a GROUP BY clause or ORDER BY clause
• A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
• The WHERE clause of a SELECT statement
• The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• The condition of a CHECK constraint

Version Query Pseudocolumns:

The version query pseudocolumns are valid only in Oracle Flashback Version Query, which is a form of Oracle Flashback Query. The version query 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).

COLUMN_VALUE Pseudocolumn:

When you refer to an XMLTable construct without the COLUMNS clause, or when you use the TABLE function to refer to a scalar nested table type, the database returns a virtual table with a single column. This name of this pseudocolumn is COLUMN_VALUE.

In the context of XMLTable, the value returned is of datatype XMLType.

OBJECT_ID Pseudocolumn:

The OBJECT_ID pseudocolumn returns the object identifier of a column of an object table or view. Oracle uses this pseudocolumn as the primary key of an object table. OBJECT_ID is useful in INSTEAD OF triggers on views and for identifying the ID of a substitutable row in an object table

Object_Value Pseudocolumn:

The OBJECT_VALUE pseudocolumn returns system-generated names for the columns of an object table, XMLType table, object view, or XMLType view. This pseudocolumn is useful for identifying the value of a substitutable row in an object table and for creating object views with the WITH OBJECT IDENTIFIER clause.

ROWID Pseudocolumn:

For each row in the database, the ROWID pseudocolumn returns the address of the row.

Oracle Database rowid values contain information necessary to locate a row:

• The data object number of the object
• The data block in the datafile in which the row resides
• The position of the row in the data block (first row is 0)
• The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Values of the ROWID pseudocolumn have the datatype ROWID or UROWID. Refer to “Rowid Datatypes” and “UROWID Datatype” for more information.

Rowid values have several important uses:

• They are the fastest way to access a single row.
• They can show you how the rows in a table are stored.
• They are unique identifiers for rows in a table.

You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

ROWNUM Pseudocolumn:

The ROW_NUMBER built-in SQL function provides superior support for ordering the results of a query.

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed.

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.

Using ROWNUM in a query can affect view optimization

 

© 2015, https:. All rights reserved. On republishing this post, you must provide link to original post