Pseudocolumns

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. This section describes these pseudocolumns:

• CURRVAL and NEXTVAL
• LEVEL
• ROWID
• ROWNUM
• XMLDATA

CURRVAL and NEXTVAL

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

Where to Use Sequence Values

You can use CURRVAL and NEXTVAL in:

• 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
• DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
• The condition of a CHECK constraint

Also, within a single SQL statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

How to Use Sequence Values

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence only once:

• For each row returned by the outer query block of a SELECT statement. Such a query block can appear in the following places:
  • A top-level SELECT statement
  • An INSERT ... SELECT statement (either single-table or multi-table). For a multi-table insert, the reference to NEXTVAL must appear in the VALUES clause, and the sequence is updated once for each row returned by the subquery, even though NEXTVAL may be referenced in multiple branches of the multi-table insert.
  • A CREATE TABLE ... AS SELECT statement
  • A CREATE MATERIALIZED VIEW ... AS SELECT statement
• For each row updated in an UPDATE statement
• For each INSERT statement containing a VALUES clause
• For row "merged" (either inserted or updated) in a MERGE statement. The reference to NEXTVAL can appear in the merge_insert_clause or the merge_update_clause.

If any of these locations contains more than one reference to NEXTVAL, then Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL.

If any of these locations contains references to both CURRVAL and NEXTVAL, then Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL.

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

Finding the current value of a sequence: Example

This example selects the next value of the employee sequence in the sample schema hr:

SELECT employees_seq.nextval 
    FROM DUAL;

Inserting sequence values into a table: Example

This example increments the employee sequence and uses its value for a new employee inserted into the sample table hr.employees:

INSERT INTO employees
   VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', 
   '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null,
   30);

Reusing the current value of a sequence: Example

This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table:

INSERT INTO orders (order_id, order_date, customer_id)
   VALUES (orders_seq.nextval, TO_DATE(SYSDATE), 106);

INSERT INTO order_items (order_id, line_item_id, product_id)
   VALUES (orders_seq.currval, 1, 2359);

INSERT INTO order_items (order_id, line_item_id, product_id)
   VALUES (orders_seq.currval, 2, 3290);

INSERT INTO order_items (order_id, line_item_id, product_id)
   VALUES (orders_seq.currval, 3, 2381);

LEVEL

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. Figure 2-1 shows the nodes of an inverted tree with their LEVEL values.

Figure 2-1 Hierarchical Tree

Text description of the illustration sql_elements78a.gif

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

Restriction on LEVEL in WHERE Clauses

In a [NOT] IN condition in a WHERE clause, if the right-hand side of the condition is a subquery, you cannot use LEVEL on the left-hand side of the condition. However, you can specify LEVEL in a subquery of the FROM clause to achieve the same result. For example, the following statement is not valid:

SELECT employee_id, last_name FROM employees
   WHERE (employee_id, LEVEL) 
      IN (SELECT employee_id, 2 FROM employees)
   START WITH employee_id = 2
   CONNECT BY PRIOR employee_id = manager_id;

But the following statement is valid because it encapsulates the query containing the LEVEL information in the FROM clause:

SELECT v.employee_id, v.last_name, v.lev 
   FROM 
      (SELECT employee_id, last_name, LEVEL lev 
      FROM employees v
      START WITH employee_id = 100 
      CONNECT BY PRIOR employee_id = manager_id) v 
   WHERE (v.employee_id, v.lev) IN
      (SELECT employee_id, 2 FROM employees); 

ROWID

For each row in the database, the ROWID pseudocolumn returns a row's address. Oracle9i rowid values contain information necessary to locate a row:

• The data object number of the object
• Which data block in the datafile
• Which row in the data block (first row is 0)
• Which datafile (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.

Rowid values have several important uses:

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

You should not use ROWID as a table's primary key. 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.

Example

This statement selects the address of all rows that contain data for employees in department 20:

SELECT ROWID, last_name  
   FROM employees
   WHERE department_id = 20;

ROWNUM

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. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

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. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

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. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
    WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table
    SET column1 = ROWNUM;

XMLDATA

Oracle stores XMLType data either in LOB or object-relational columns, based on XMLSchema information and how you specify the storage clause. The XMLDATA pseudocolumn lets you access the underlying LOB or object relational column to specify additional storage clause parameters, constraints, indexes, and so forth.

Example

The following statements illustrate the use of this pseudocolumn. Suppose you create a simple table of XMLType:

CREATE TABLE xml_lob_tab of XMLTYPE;

The default storage is in a CLOB column. To change the storage characteristics of the underlying LOB column, you can use the following statement:

ALTER TABLE xml_lob_tab MODIFY LOB (XMLDATA)
   (STORAGE (BUFFER_POOL DEFAULT) CACHE);

Now suppose you have created an XMLSchema-based table like the xwarehouses table created in "Using XML in SQL Statements". You could then use the XMLDATA column to set the properties of the underlying columns, as shown in the following statement:

ALTER TABLE xwarehouses ADD (UNIQUE(XMLDATA."WarehouseId"));