Advanced Sql Queries- Lesson: Pseudo Columns & Functions

Advanced Sql Queries

Lesson: Pseudo Columns & Functions

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based

 on Webucator's Advanced Oracle SQL Queries course.

Oracle SQL can do more than simply retrieve data from tables. There are features

 of the language which allow certain values that are not explicitly included in the 

database to be derived or calculated. This chapter will explore several of the 

features of the language which provide these capabilities.

Lesson Goals

• Use the Dual table.
• Use functions.
• Use Pseudo-columns.
• Use Rowid.
• Use Rownum.

Using the Dual Table

The DUAL table is a special table supplied as part of the Oracle data dictionary. This table 

has one column called DUMMY and a single row containing the value X. Oracle processes 

and user-defined programs can reference this table to retrieve a single row (which is useful 

for executing functions).

Functions

To test a function within a SQL query, use the DUAL table and supply the function name in 

the SELECT clause of the query.

SELECT <function call> FROM dual;

The SYS_CONTEXT function returns the value of a supplied parameter related to the 

context namespace. It can be used to retrieve information about the user'senvironment 

using the USERENV namespace. It also can be used to retrieve application specific data 

by using user defined context namespaces.

Code Sample:

Pseudo-Columns-and-Functions/Demos/select_from_dual.sql

SELECT sys_context('USERENV' , 'SESSION_USER') 
FROM dual;

This example returns the name of the current user using the sys_context FUNCTION. 

It demonstrates the typical use of the dual table as well as one example of how 

SYS_CONTEXT can be called.

Use the SYSCONTEXT Function

Duration: 10 to 15 minutes.

1. Write queries that use the sys_context function to retrieve the IP_ADDRESS, HOST, 
2. LANGUAGE, ISDBA, SESSION_USER, DB_NAME values which are associated with 
3. the USERENV namespace.

Solution:

Pseudo-Columns-and-Functions/Solutions/syscontext_solutions.sql

--
-- Examples using the sys_context function
--
SELECT sys_context('USERENV' , 'IP_ADDRESS')
FROM dual;

SELECT sys_context('USERENV', 'HOST')
FROM dual;

SELECT sys_context('USERENV', 'LANGUAGE')
FROM dual;

SELECT sys_context('USERENV', 'ISDBA')
FROM dual;

SELECT sys_context('USERENV', 'SESSION_USER')
FROM dual;

SELECT sys_context('USERENV', 'DB_NAME')
FROM dual;

Pseudo-Columns

A pseudo-column is not an actual column in a table. It is like a function in that it returns a 

value when selected. Pseudocolumns exist that provide functionality related to hierarchical

 queries, sequences (CURRVAL and NEXTVAL), flashback queries, system generated

identifiers, and XML.

Using Rowid

A Rowid provide quick access to a row. It is a unique identifier for a row within a cluster 

(not necessarily the entire database). It is the fastest possible access to a row of a given 

table. To retrieve a Rowid use the ROWID pseudocolumn in the SELECT clause. 

To access a row using Rowid use the ROWID pseudocolumn in the WHERE clause. 

A Rowid is not necessarily unique throughout the database.

PLEASE NOTE: The following example involves the use of a cluster (an object that contains

 data from one or more tables). Use of clusters is an advanced topic related to performance 

tuning. It is only introduced here to demonstrate that there is not a guarantee that a rownum 

will be unique throughout the entire database.

Often a Rowid value uniquely identifies a row for the entire database. However, rows in 

different tables and stored in the same cluster might have duplicate Rowids. In the following

 example, a DBA may query a data dictionary table (which holds metadata about the 

database itself). A number of tables are returned that share the same cluster name (C_OBJ#).

SELECT cluster_name, table_name
FROM dba_tables WHERE cluster_name='C_OBJ#';

A DBA could now query two of the objects and show the resulting Rowids that appear in 

both clusters.

SELECT rowid
FROM sys.icol$
INTERSECT
SELECT rowid
FROM sys.ind$;

Remember, although Rowids are often unique, they are not guaranteed to be unique in 

the entire database - only a given cluster.

Using Rownum

The ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects

 the row result of a query. It can be used to limit the results returned from a query. Unlike 

ROWID, which is associated with a row in a table, a ROWNUM is assigned when a number 

is retrieved into memory. The ROWNUM assigned remains with the row when an 

ORDER BY is used.

Code Sample:

Pseudo-Columns-and-Functions/Demos/rownum_query1.sql

SELECT * 
FROM departments 
WHERE rownum < 10;

ROWNUM can be used to limit the number of rows returned by a query.

However, be aware that an ORDER BY will reorder the columns.

Code Sample:

Pseudo-Columns-and-Functions/Demos/rownum_query2.sql

SELECT department_name, rownum
FROM departments;
				
SELECT department_name, rownum 
FROM departments 
ORDER BY department_name;

SELECT department_name, rownum 
FROM departments 
WHERE rownum < 10 
ORDER BY department_name;

The first two queries show the results of a SELECT without and then with an ORDER BY 

clause without a ROWNUM reference in the WHERE clause. Note the first few rows 

returned in each query. As expected the sort order is different. However, notice that the 

values in the ROWNUM column are consistent.

The third query limits by ROWNUM, which the RDBMS processes prior to the ORDER BY. 

You will see that, although the rows returned are in order by department_name, they are not

 the first ten rows from the previous query. They are, however, the rows with the ROWNUM 

values 1-10. Also notice that these are consistent values with the first two queries.

Code Sample:

Pseudo-Columns-and-Functions/Demos/rownum_query3.sql

SELECT * FROM (
	SELECT * 
	FROM departments 
	ORDER BY department_name
) WHERE rownum < 10;

You can force the order by to follow the rownum limitation by using a subquery. Subqueries 

will be covered in an upcoming chapter on using subqueries. For now, simply be aware that

 there is a way to cause rownums to be evaluated after an ORDER BY clause. Also, bear in 

mind that this query will retrieve all of the data from the table listed in the inner query 

because it is selecting all columns from all rows. This might result in a significant performanc

e issue. In class where you are the only person connected to the database, using 

SELECT * is acceptable. However when in a production environment, limit its use due to the

 performance issue.

Using ROWNUM in UPDATE Statements

ROWNUM can be used to assign unique values to each row in a table when using an 

UPDATE statement.

Code Sample:

Pseudo-Columns-and-Functions/Demos/rownum_update.sql

CREATE TABLE mytable AS SELECT * FROM departments;
ALTER TABLE mytable ADD test_col number; 

UPDATE mytable SET test_col=rownum;  

SELECT * 
FROM mytable;

In this example, we first create a table with the structure and content of the departments 

table. Next, we add an additional column to the table that we will be populating. Both of 

these actions will be described in greater detail in an upcoming lesson on Data Definition 

Language (DDL).

Now an update statement is run. We can verify the results by running a simple SELECT 

query on the table and see that a different number was assigned to the new column in each

 row.

Though ROWNUM is commonly used to limit results, be aware of the behavior related to 

ordering mentioned earlier. One common misuse of ROWNUM is to limit data returned to 

a single row. If not correctly specified, an arbitrary or incorrect row may be returned when 

the desired result is the first row based upon a specified order.

The ROW_NUMBER function can be used to assign numbers in this manner as well.

Use ROWNUM

Duration: 10 to 15 minutes.

1. Get a list of all department names in the departments tables
2. Add the ROWNUM pseudo-column to the SELECT clause
3. Add an ORDER BY department_name. Notice that the rownum is retreived prior to 
4. ordering the results.
5. Add ROWNUM to the WHERE clause to limit the results to those with ROWNUMS 
6. less than five.

Solution:

Pseudo-Columns-and-Functions/Solutions/rownum_solutions.sql

--Initial list of department names

SELECT department_name 
FROM departments;

--Add ROWNUM to the SELECT clause

SELECT rownum, department_name 
FROM departments;
				  
--Add ORDER BY department_name
SELECT rownum, department_name 
FROM departments 
ORDER BY department_name;
				  
--Although we have not yet covered subqueries, note that you can force the ROWNUMS 
--to be assigned after the order by by using a subquery:

SELECT department_name, rownum 
FROM
    (
      SELECT department_name 
      FROM departments 
      ORDER BY department_name
    );
				  
-- Add ROWNUM in the WHERE clause to limit to those less than five.
SELECT * 
FROM departments 
WHERE ROWNUM < 5;