The LEVEL pseudocolumn can be used in a SELECT statement that performs a hierarchical query. For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root node, 2 for a child of a root, and so on. In a hierarchical query, a root node is the highest node within an inverted tree, a child node is any non-root node, a parent node is any node that has children, and a leaf node is any node without children.
The number of levels returned by a hierarchical query is limited to 32.
The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is PRESIDENT. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart,
empno, mgr, job
START WITH job = 'PRESIDENT'
CONNECT BY PRIOR empno = mgr;
Returns the following result:
ORG_CHART EMPNO MGR JOB
------------------ --------- --------- ---------
JONES 7566 7839 MANAGER
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
BLAKE 7698 7839 MANAGER
WARD 7521 7698 SALESMAN
JAMES 7900 7698 CLERK
TURNER 7844 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
MARTIN 7654 7698 SALESMAN
14 rows selected.
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle Lite 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.
If an ORDER BY clause follows ROWNUM in the same subquery, the rows are 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 Lite to use an index to access the data, Oracle Lite may retrieve the rows in a different order than without the index.
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. See Example 3.
The following example uses ROWNUM to limit the number of rows returned by a query:
The following example follows the ORDER BY clause with ROWNUM in the same query. As a result, the rows are reordered by the ORDER BY clause and do not have the same effect as the preceding example:
The following query returns the ten smallest employee numbers. This is sometimes referred to as a "top-N query":
The following query returns no rows:
The first fetched row 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, this makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
The following statement assigns unique values to each row of a table:
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:
BY clause follows
ROWNUM in the same query, then the rows will be reordered by the
BY clause. The results can vary depending on the way the rows are accessed. For example, if the
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:
If you embed the
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 employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:
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:
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.
Please refer to the function ROW_NUMBER for an alternative method of assigning unique numbers to rows.
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
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
See Also:Oracle Database Object-Relational Developer's Guide for examples of the use of this pseudocolumn
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
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);
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. Please refer to
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
ORA_ROWSCN is not supported for Flashback Query. Instead, use the version query pseudocolumns, which are provided explicitly for Flashback Query. Please refer to the
SELECT ... flashback_query_clause for information on Flashback Query and "Version Query Pseudocolumns" for additional information on those pseudocolumns.
Restriction: This pseudocolumn is not supported for external tables.
Example The first statement below uses the
ORA_ROWSCN pseudocolumn to get the system change number of the last operation on the
employees table. The second statement uses the pseudocolumn with the
SCN_TO_TIMESTAMP function to determine the timestamp of the operation:
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.
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
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
Example This statement selects the address of all rows that contain data for employees in department 20:
SELECT ROWID, last_name
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
In the context of
XMLTable, the value returned is of datatype
XMLType. For example, the following two statements are equivalent, and the output for both shows
COLUMN_VALUE as the name of the column being returned:
In the context of a
TABLE function, the value returned is the datatype of the collection element. The following statements create the two levels of nested tables illustrated in "Multi-level Collection Example" to show the uses of
COLUMN_VALUE in this context:
The next statement uses
COLUMN_VALUE to select from the
In a nested type, you can use the
COLUMN_VALUE pseudocolumn in both the select list and the
COLUMN_VALUE is also the name that Oracle Database generates for the scalar value of an inner nested table without a column or attribute name, as shown in the example that follows. In this context,
COLUMN_VALUE is not a pseudocolumn, but an actual column name.
WHERE department_id = 20;
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"));