Pseudo-column

A pseudo-column is an Oracle assigned value (pseudo-field) used in the same context as an Oracle Database column, but not stored on disk. SQL and PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: SYSDATE, SYSTIMESTAMP, ROWID, ROWNUM, UID, USER, LEVEL, CURRVAL, NEXTVAL, ORA_ROWSCN, etc.

Pseudocolumns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Also note that pseudocolumns are allowed in SQL statements, but not in procedural statements.

SYSDATE and SYSTIMESTAMP

Return the current DATE and TIMESTAMP:

SQL> SELECT sysdate, systimestamp FROM dual;
SYSDATE   SYSTIMESTAMP
--------- ----------------------------------------
13-DEC-07 13-DEC-07 10.02.31.956842 AM +02:00

UID and USER

Return the User ID and Name of a database user:

SQL> SELECT uid, user FROM dual;
       UID USER
---------- ------------------------------
        50 MICHEL

CURRVAL and NEXTVAL

A sequence is a schema object that generates sequential numbers. When you create a sequence, you can specify its initial value and an increment. CURRVAL returns the current value in a specified sequence.

Before you can reference CURRVAL in a session, you must use NEXTVAL to generate a number. A reference to NEXTVAL stores the current sequence number in CURRVAL. NEXTVAL increments the sequence and returns the next value. To obtain the current or next value in a sequence, you must use dot notation, as follows:

sequence_name.CURRVAL
sequence_name.NEXTVAL

After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. However, you can use CURRVAL and NEXTVAL only in a SELECT list, the VALUES clause, and the SET clause. In the following example, you use a sequence to insert the same employee number into two tables:

INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);

INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);

If a transaction generates a sequence number, the sequence is incremented immediately whether you commit or roll back the transaction.

LEVEL

You use LEVEL with the SELECT CONNECT BY statement to organize rows from a database table into a tree structure. LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2, grandchildren are level 3, and so on.

In the START WITH clause, you specify a condition that identifies the root of the tree. You specify the direction in which the query walks the tree (down from the root or up from the branches) with the PRIOR operator.

ROWID

ROWID returns the rowid (binary address) of a row in a database table. You can use variables of type UROWID to store rowids in a readable format. In the following example, you declare a variable named row_id for that purpose: DECLARE row_id UROWID;

When you select or fetch a physical rowid into a UROWID variable, you can use the function ROWIDTOCHAR, which converts the binary value to an 18-byte character string. Then, you can compare the UROWID variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor.

ROWNUM

ROWNUM returns a number indicating the order in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the retrieved rows before the sort is done.

You can use ROWNUM in an UPDATE statement to assign unique values to each row in a table. Also, you can use ROWNUM in the WHERE clause of a SELECT statement to limit the number of rows retrieved, as follows:

DECLARE
  CURSOR c1 IS SELECT empno, sal FROM emp
                WHERE sal > 2000 AND ROWNUM < 10; -- returns 9 rows

The value of ROWNUM increases only when a row is retrieved, so the only meaningful uses of ROWNUM in a WHERE clause are

... WHERE ROWNUM < constant;

... WHERE ROWNUM <= constant;

ORA_ROWSCN

ORA_ROWSCN returns the system change number (SCN) of the last change inside the block containing a row. It can return the last modification for the row if the table is created with the option ROWDEPENDENCIES (default is NOROWDEPENDENCIES).
The function SCN_TO_TIMESTAMP allows you to convert SCN to timestamp.

SQL> select ename, ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) from emp where empno=7369;
ENAME      ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ---------- ----------------------------------------------------------------
SMITH         2113048 20/12/2008 16:59:51.000

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#