SQL Pseudocolumns and Functions
SQL recognizes the pseudocolumns in Table 3-2, which return specific data items:
Table 3-2 Pseudocolumns and Internal Datatypes
Pseudocolumn Internal Datatype
CURRVAL
NUMBER
LEVEL
NUMBER
NEXTVAL
NUMBER
ROWID
ROWID
ROWLABEL
MLSLABEL
ROWNUM
NUMBER
Pseudocolumns are not actual columns in a table. However, pseudocolumns are treated like columns, so their values must be SELECTed from a table. Sometimes it is convenient to select pseudocolumn values from a dummy table.
In addition, SQL recognizes the parameterless functions in Table 3-3, which also return specific data items.
Table 3-3 Parameterless Functions
Function Internal Datatype
SYSDATE
DATE
UID
NUMBER
USER
VARCHAR2
You can refer to SQL pseudocolumns and functions in SELECT, INSERT, UPDATE, and DELETE statements. In the following example, you use SYSDATE to compute the number of months since an employee was hired:
EXEC SQL SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE)
INTO :months_of_service
FROM EMP
WHERE EMPNO = :emp_number;
Brief descriptions of the SQL pseudocolumns and functions follow. For details, see the Oracle Database SQL Language Reference.
CURRVAL returns the current number in a specified sequence. Before you can reference CURRVAL, you must use NEXTVAL to generate a sequence number.
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.
LEVEL is used in the SELECT CONNECT BY statement to incorporate some or all the rows of a table into a tree structure. In an ORDER BY or GROUP BY clause, LEVEL segregates the data at each level in 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. In the START WITH clause, you specify a condition that identifies the root of the tree.
NEXTVAL returns the next number in a specified sequence. After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. In the following example, you use the sequence named partno to assign part numbers:
EXEC SQL INSERT INTO PARTS
VALUES (partno.NEXTVAL, :description, :quantity, :price);
If a transaction generates a sequence number, the sequence is incremented when you commit or rollback the transaction. A reference to NEXTVAL stores the current sequence number in CURRVAL.
ROWID returns a row address in hexadecimal.
ROWNUM returns a number indicating the sequence 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 selected rows before the sort is done.
You can use ROWNUM to limit the number of rows returned by a SELECT statement. Also, you can use ROWNUM in an UPDATE statement to assign unique values to each row in a table. Using ROWNUM in the WHERE clause does not stop the processing of a SELECT statement; it just limits the number of rows retrieved. The only meaningful use of ROWNUM in a WHERE clause is
… WHERE ROWNUM < constant;
because the value of ROWNUM increases only when a row is retrieved. The following search condition can never be met because the first four rows are not retrieved:
… WHERE ROWNUM = 5;
SYSDATE returns the current date and time.
UID returns the unique ID number assigned to an Oracle user.
USER returns the username of the current Oracle user.
ROWLABEL Column
SQL also recognizes the special column ROWLABEL, which Trusted Oracle database version 7 creates for every database table. Like other columns, ROWLABEL can be referenced in SQL statements. However, with standard Oracle, ROWLABEL returns a null. With Trusted Oracle database version 7, ROWLABEL returns the operating system label for a row.
A common use of ROWLABEL is to filter query results. For example, the following statement counts only those rows with a security level higher than "unclassified":
EXEC SQL SELECT COUNT(*) INTO :head_count FROM EMP
WHERE ROWLABEL > 'UNCLASSIFIED';
For more information about the ROWLABEL column, see the Trusted Oracle database version 7 Server Administrator's Guide.
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.
Contents [hide]
1 SYSDATE and SYSTIMESTAMP
2 UID and USER
3 CURRVAL and NEXTVAL
4 LEVEL
5 ROWID
6 ROWNUM
7 ORA_ROWSCN
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
[edit]UID and USER
Return the User ID and Name of a database user:
SQL> SELECT uid, user FROM dual;
UID USER
-- ------
0 SYS
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.
[edit]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 10 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
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. For example, the following two statements are equivalent, and the output for both shows COLUMN_VALUE as the name of the column being returned:
SELECT * FROM XMLTABLE('<a>123</a>');
COLUMN_VALUE
<a>123</a>
SELECT COLUMN_VALUE FROM (XMLTable('<a>123</a>'));
COLUMN_VALUE
<a>123</a>
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:
CREATE TYPE phone AS TABLE OF NUMBER;
/
CREATE TYPE phone_list AS TABLE OF phone;
/
The next statement uses COLUMN_VALUE to select from the phone type:
SELECT t.COLUMN_VALUE from table(phone(1,2,3)) t;
COLUMN_VALUE
1
2
3
In a nested type, you can use the COLUMN_VALUE pseudocolumn in both the select list and the TABLE function:
SELECT t.COLUMN_VALUE FROM
TABLE(phone_list(phone(1,2,3))) p, TABLE(p.COLUMN_VALUE) t;
COLUMN_VALUE
1
2
3
The keyword 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.
CREATE TABLE my_customers (
cust_id NUMBER,
name VARCHAR2(25),
phone_numbers phone_list,
credit_limit NUMBER)
NESTED TABLE phone_numbers STORE AS outer_ntab
(NESTED TABLE COLUMN_VALUE STORE AS inner_ntab);
See Also:
XMLTABLE for information on that function
table_collection_expression ::= for information on the TABLE function
ALTER TABLE examples in "Nested Tables: Examples"
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.
See Also:
Oracle Database Object-Relational Developer's Guide for examples of the use of this pseudocolumn
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.
See Also:
object_table and object_view_clause for more information on the use of this pseudocolumn
Oracle Database Object-Relational Developer's Guide for examples of the use of this pseudocolumn
XMLDATA Pseudocolumn
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"));
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.
See Also:
"Hierarchical Queries" for more information about the NOCYCLE parameter and "Hierarchical Query Examples" for an example that uses the CONNECT_BY_ISCYCLE pseudocolumn
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. This information indicates whether a given row can be further expanded to show more of the hierarchy.
CONNECT_BY_ISLEAF Example
The following example shows the first three levels of the hr.employees table, indicating for each row whether it is a leaf row (indicated by 1 in the IsLeaf column) or whether it has child rows (indicated by 0 in the IsLeaf column):
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
Employee IsLeaf LEVEL Path
----- -- -- -----
Abel 1 3 /King/Zlotkey/Abel
Ande 1 3 /King/Errazuriz/Ande
Banda 1 3 /King/Errazuriz/Banda
Bates 1 3 /King/Cambrault/Bates
Bernstein 1 3 /King/Russell/Bernstein
Bloom 1 3 /King/Cambrault/Bloom
Cambrault 0 2 /King/Cambrault
Cambrault 1 3 /King/Russell/Cambrault
Doran 1 3 /King/Partners/Doran
Errazuriz 0 2 /King/Errazuriz
Fox 1 3 /King/Cambrault/Fox
…
See Also:
"Hierarchical Queries" and SYS_CONNECT_BY_PATH