Pseudo Columns 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.

Following are the types:

1.    Sequence Pseudocolumns

2.    Version Query Pseudocolumns

3.    ROWID Pseudocolumn

4.    ROWNUM Pseudocolumn

5.    ORA_ROWSCN Pseudocolumn

6.    OBJECT_ID Pseudocolumn

7.    OBJECT_VALUE Pseudocolumn

8.    COLUMN_VALUE Pseudocolumn

9.    XMLDATA Pseudocolumn

10. Hierarchical Query Pseudocolumns

Sequence Pseudocolumns:

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys

·         CURRVAL: Returns the current value of a sequence

·         NEXTVAL: Increments the sequence and returns the next value

create sequence s320;

After sequence created if we issue currval statement then we will get the following error ORA-08002.

select s320.currval from dual;

ORA-08002: sequence S320.CURRVAL is not yet defined in this session

So first issue .nextval and then issue .currval to avoid errors.

select s320.nextval from dual;

select s320.currval from dual;

Note: Sequence value generated with nextval pseudo column is specific to a session. If select s320.currval from dual; executed in another session before to .nextval then it will throw ORA-08002 error.

Version Query Pseudocolumns:

·         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).

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

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.

ROWNUM Pseudocolumn:

ROWNUM function returns sequence numbers when used in select statement.

select * from emp where rownum <5 -- gives 4 rows

select * from emp where rownum = 5 -- gives no rows

select * from emp where rownum = 0 -- gives no rows

select * from emp where rownum is null -- gives no rows

select * from emp where rownum = null -- gives no rows

select * from emp where rownum > 5 -- gives no rows

ORA_ROWSCN Pseudocolumn:

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Refer to CREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.

You cannot use this pseudocolumn in a query to a view. However, you can use it to refer to the underlying table when creating a view. You can also use this pseudocolumn in the WHERE clause of an UPDATE or DELETE statement

SELECT ORA_ROWSCN, ename FROM emp;

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.

Note:

In earlier releases, this pseudocolumn was called SYS_NC_OID$. That name is still supported for backward compatibility. However, Oracle recommends that you use the more intuitive name OBJECT_ID.

create type emp_obj as object ( empno number, ename varchar2(40) );

sql> create table emp_obj_tab of emp_obj;

Table created.

sql> desc emp_obj_tab;

 Name                                      Null?    Type

 ----------------------------------------- -------- --------------

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

sql> insert into emp_obj_tab values (1,'A');

1 row created.

sql> insert into emp_obj_tab values (2, 'B');

1 row created.

sql> commit;

Commit complete.

sql> select SYS_NC_OID$, empno, ename from emp_obj_tab;

SYS_NC_OID$                           EMPNO ENAME

-------------------------------- ---------- --------------

EC88A0EDDFAE65A9E04400212814ED91          1 A

EC88A0EDDFAF65A9E04400212814ED91          2 B

sql> select object_id, empno, ename from emp_obj_tab;

OBJECT_ID                             EMPNO ENAME

-------------------------------- ---------- --------------

EC88A0EDDFAE65A9E04400212814ED91          1 A

EC88A0EDDFAF65A9E04400212814ED91          2 B

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.

Note:

In earlier releases, this pseudocolumn was called SYS_NC_ROWINFO$. That name is still supported for backward compatibility. However, Oracle recommends that you use the more intuitive name OBJECT_VALUE.

sql> select object_value from emp_obj_tab;

OBJECT_VALUE(EMPNO, ENAME)

----------------------------------

EMP_OBJ(1, 'A')

EMP_OBJ(2, 'B')

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

sql> CREATE TYPE phone AS TABLE OF NUMBER;

  2  /

Type created.

sql> CREATE TYPE phone_list AS TABLE OF phone;

  2  /

Type created.

sql> SELECT t.COLUMN_VALUE

  2  FROM TABLE(phone(1,2,3)) t;

COLUMN_VALUE

------------

           1

           2

           3