Exploring Indexes Concepts in Oracle

About Indexes

Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows.

Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries. Otherwise, they just take up space and add overhead when the indexed columns are updated. You should use the EXPLAIN PLAN feature to determine how the indexes are being used in your queries. Sometimes, if an index is not being used by default, you can use a query hint so that the index is used.

Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.

The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value.

You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns if you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:

CREATE INDEX employees_idx1 ON employees (last_name, job_id);

CREATE INDEX employees_idx2 ON employees (job_id, last_name);

If you create index on a column combination which has already been indexed, you will get following errors:

SQL> create index emp_pkk on emp(empno);

create index emp_pkk on emp(empno)

                            *

ERROR at line 1:

ORA-01408: such column list already indexed

Oracle Database automatically maintains and uses indexes after they are created. Oracle Database automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.

Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle Database must also update the indexes associated with the table.

The optimizer can use an existing index to build another index. This results in a much faster index build.

Classification of Indexes:

On bases of Logical design Indexes are classified as:

■ Unique and Nonunique Indexes

■ Visible and Invisible Indexes

■  Single Column or Composite Indexes

■ Function-Based Indexes

On bases of Physical Implementation Indexes are classified as:

■ B-Tree: Normal or Reverse Key

■ Bitmap Indexes

■ Bitmap Join Indexes
■ Partition or Non Partitioned

Single Column or Composite Indexes

Index could be created on a single column or on multiple columns. 

A composite index (also called a concatenated index) is an index that you create on multiple columns in a table. Columns in a composite index can appear in any order and need not be adjacent in the table.

Composite indexes can speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index. Therefore, the order of the columns used in the definition is important.

Generally, the most commonly accessed or most selective columns go first.

No more than 32 columns can form a regular composite index. For a bitmap index, the maximum number columns is 30. A key value cannot exceed roughly half (minus some overhead) the available data space in a data block.

Unique and Non-unique Indexes

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values. 

Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique index:

CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
      TABLESPACE indx;

Alternatively, you can define UNIQUE integrity constraints on the desired columns. The database enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly.

Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

You cannot create Unique index on column which has duplicate records:

Table: EMP
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
      7566 JONES      MANAGER         7839 02-APR-81       2975
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
      7788 SCOTT      ANALYST         7566 19-APR-87       3000
      7839 KING       PRESIDENT            17-NOV-81       5000
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
      7876 ADAMS      CLERK           7788 23-MAY-87       1100

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950
      7902 FORD       ANALYST         7566 03-DEC-81       3000
      7934 MILLER     CLERK           7782 23-JAN-82       1300

14 rows selected.


SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(DEPTNO);
CREATE UNIQUE INDEX EMP_D_i ON EMP(DEPTNO)
                               *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Indexes and Nulls

NULL values in indexes are considered to be distinct except when all the non-NULL values in two or more rows of an index are identical, in which case the rows are considered to be identical. Therefore, UNIQUE indexes prevent rows containing NULL values from being treated as identical. This does not apply if there are no non-NULL values—in other words, if the rows are entirely NULL. Oracle Database does not index table rows in which all key columns are NULL, except in the case of bitmap indexes or when the cluster key column value is NULL.

Unique Indexes doesn't identify NULL values. So even if a column has all or few NULL values we are still able to create Unique index on it.

SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(comm);
Index created.
SQL> CREATE UNIQUE INDEX EMP_D_i ON EMP(deptno);
Index created.

Function-Based Indexes

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of the function or expression and stores it in the index. You can create a function-based index as either a B-tree or a bitmap index.

The expression cannot contain any aggregate functions, and it must be DETERMINISTIC. For building an index on a column containing an object type, the function can be a method of that object, such as a map method. However, you cannot build a function-based index on a LOB column, REF, or nested table column, nor can you build a function-based index if the object type contains a LOB, REF, or nested table.

Uses of Function-Based Indexes
Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses. The value of the expression is computed and
stored in the index. When it processes INSERT and UPDATE statements, however, Oracle Database must still evaluate the function to process the statement.

For example, if you create the following index:
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
Oracle Database can use it when processing queries such as this:
SELECT a FROM table_1 WHERE a + b * (c - 1) < 100;

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index: 
CREATE INDEX uppercase_idx ON employees (UPPER(first_name));
can facilitate processing queries such as this:

SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD';

Bitmap Indexes

The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle Database stores each key value repeatedly with each stored rowid. In a bitmap index, a bitmap for each key value is used instead of a list of rowids.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient.

In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:

• The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.
• The indexed table is either read-only or not subject to significant modification by DML statements.

Bitmap Indexes on a Single Table

Example 2 Query of customers Table

SQL> SELECT cust_id, cust_last_name, cust_marital_status, cust_gender
  2  FROM   sh.customers 
  3  WHERE  ROWNUM < 8 ORDER BY cust_id;
 
   CUST_ID CUST_LAST_ CUST_MAR C
---------- ---------- -------- -
         1 Kessel              M
         2 Koch                F
         3 Emmerson            M
         4 Hardy               M
         5 Gowen               M
         6 Charles    single   F
         7 Ingram     single   F
 
7 rows selected.

The cust_marital_status and cust_gender columns have low cardinality, whereas cust_id and cust_last_name do not. Thus, bitmap indexes may be appropriate on cust_marital_status and cust_gender. A bitmap index is probably not useful for the other columns. Instead, a unique B-tree index on these columns would likely provide the most efficient representation and retrieval.

Table 1-2 illustrates the bitmap index for the cust_gender column output shown in Example 2. It consists of two separate bitmaps, one for each gender.

Table 1-2 Sample Bitmap

Value	Row 1	Row 2	Row 3	Row 4	Row 5	Row 6	Row 7
M	1	0	1	1	1	0	0
F	0	1	0	0	0	1	1

A mapping function converts each bit in the bitmap to a rowid of the customers table. Each bit value depends on the values of the corresponding row in the table. For example, the bitmap for the M value contains a 1 as its first bit because the gender is M in the first row of the customers table. The bitmapcust_gender='M' has a 0 for its the bits in rows 2, 6, and 7 because these rows do not contain M as their value.

DBA_INDEX, ALL_INDEXES, USER_INDEXES Data Dictionary Tables:

We use these data dictionary tables to know about created indexes:

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the index
INDEX_NAME	VARCHAR2(30)	NOT NULL	Name of the index
INDEX_TYPE	VARCHAR2(27)		Type of the index: NORMAL BITMAP FUNCTION-BASED NORMAL FUNCTION-BASED BITMAP DOMAIN
TABLE_OWNER	VARCHAR2(30)	NOT NULL	Owner of the indexed object
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the indexed object
TABLE_TYPE	CHAR(5)		Type of the indexed object (for example, TABLE, CLUSTER)
UNIQUENESS	VARCHAR2(9)		Indicates whether the index is UNIQUE or NONUNIQUE
COMPRESSION	VARCHAR2(8)		Indicates whether index compression is enabled (ENABLED) or not (DISABLED)

Using GOTO and NULL statement

The GOTO statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block. In the following example, you go to an executable statement farther down in a sequence of statements:

BEGIN
...
GOTO insert_row;
...
<>
INSERT INTO emp VALUES ...
END;
In the next example, you go to a PL/SQL block farther up in a sequence of statements:
DECLARE
x NUMBER := 0;
BEGIN
<>
BEGIN
x := x + 1;
END;
IF x < 10 THEN
GOTO increment_x;
END IF;
END;

The label end_loop in the following example is not allowed because it does not precede an executable statement:

DECLARE
done BOOLEAN;
BEGIN
FOR i IN 1..50 LOOP
IF done THEN
GOTO end_loop;
END IF;
<> -- not allowed
END LOOP; -- not an executable statement
END;
To correct the previous example, add the NULL statement::
FOR i IN 1..50 LOOP
IF done THEN

GOTO end_loop;
END IF;
...
<>
NULL; -- an executable statement
END LOOP;

As the following example shows, a GOTO statement can branch to an enclosing block from the current block:

DECLARE
my_ename CHAR(10);
BEGIN
<>
SELECT ename INTO my_ename FROM emp WHERE ...
BEGIN
GOTO get_name; -- branch to enclosing block
END;
END;

The GOTO statement branches to the first enclosing block in which the referenced label

appears.

Restrictions on the GOTO Statement

Some possible destinations of a GOTO statement are not allowed. Specifically, a GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or

sub-block. For example, the following GOTO statement is not allowed:

BEGIN

GOTO update_row; -- can't branch into IF statement

IF valid THEN

<>

UPDATE emp SET ...

END IF;

END;

As the example below shows, a GOTO statement cannot branch from one IF statement clause to another. Likewise, a GOTO statement cannot branch from one CASE statement WHEN clause to another.

BEGIN
   ...
   IF valid THEN
      ...
      GOTO update_row;  -- can't branch into ELSE clause
   ELSE
      ...
      <>
      UPDATE emp SET ...
   END IF;
END;

The next example shows that a GOTO statement cannot branch from an enclosing block into a sub-block:

BEGIN
   ...
   IF status = 'OBSOLETE' THEN
      GOTO delete_part;  -- can't branch into sub-block
   END IF;
   ...
   BEGIN
      ...
      <>
      DELETE FROM parts WHERE ...
   END;
END;

Also, a GOTO statement cannot branch out of a subprogram, as the following example shows:

DECLARE
   ...
   PROCEDURE compute_bonus (emp_id NUMBER) IS
   BEGIN
      ...
      GOTO update_row;  -- can't branch out of subprogram
   END;
BEGIN
   ...
   <>
   UPDATE emp SET ...
END;

Finally, a GOTO statement cannot branch from an exception handler into the current block. For example, the following GOTO statement is not allowed:

DECLARE
   ...
   pe_ratio  REAL;
BEGIN
   ...
   SELECT price / NVL(earnings, 0) INTO pe_ratio FROM ...
   <>
   INSERT INTO stats VALUES (pe_ratio, ...);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      pe_ratio := 0;
      GOTO insert_row;  -- can't branch into current block
END;

However, a GOTO statement can branch from an exception handler into an enclosing block.

NULL Statement

The NULL statement does nothing other than pass control to the next statement. In a conditional construct, the NULL statement tells readers that a possibility has been considered, but no action is necessary. In the following example, the NULL statement shows that no action is taken for unnamed exceptions:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      ROLLBACK;
   WHEN VALUE_ERROR THEN
      INSERT INTO errors VALUES ...
      COMMIT;
   WHEN OTHERS THEN
      NULL;
END;

In IF statements or other places that require at least one executable statement, the NULL statement to satisfy the syntax. In the following example, the NULL statement emphasizes that only top-rated employees get bonuses:

IF rating > 90 THEN
   compute_bonus(emp_id);
ELSE
   NULL;
END IF;

Also, the NULL statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that lets you defer the definition of a procedure or function until you test and debug the main program. In the following example, the NULL statement meets the requirement that at least one statement must appear in the executable part of a subprogram:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
BEGIN
   NULL;
END debit_account;

Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute

PL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement. This mechanism enables a bulk-bind operation to save information about exceptions and continue processing.

To have a bulk bind complete despite errors, add the keywords SAVE EXCEPTIONS to your FORALL statement after the bounds, before the DML statement. You should also provide an exception handler to track the exceptions that occurred during the bulk operation.

Example 11-9 shows how you can perform a number of DML operations, without stopping if some operations encounter errors. In the example, EXCEPTION_INIT is used to associate the dml_errors exception with the ORA-24381 error. The ORA-24381 error is raised if any exceptions are caught and saved after a bulk operation.

All exceptions raised during the execution are saved in the cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:

%BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration of the FORALL statement during which the exception was raised.

%BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.

The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.

The individual error messages, or any substitution arguments, are not saved, but the error message text can looked up using ERROR_CODE with SQLERRM as shown in Example 11-9.

You might need to work backward to determine which collection element was used in the iteration that caused an exception. For example, if you use the INDICES OF clause to process a sparse collection, you must step through the elements one by one to find the one corresponding to %BULK_EXCEPTIONS(i).ERROR_INDEX. If you use the VALUES OF clause to process a subset of elements, you must find the element in the index collection whose subscript matches %BULK_EXCEPTIONS(i).ERROR_INDEX, and then use that element's value as the subscript to find the erroneous element in the original collection. For examples showing how to find the erroneous elements when using the INDICES OF and VALUES OF clauses, see the PL/SQL sample programs at http://www.oracle.com/technology/tech/pl_sql/.

If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when an exception is raised. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record. If no exception is raised during execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.

Example 11-9 Bulk Operation That Continues Despite Exceptions

-- create a temporary table for this example

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE

   TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;

   emp_sr empid_tab;

-- create an exception handler for ORA-24381

   errors NUMBER;

   dml_errors EXCEPTION;

   PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN

   SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp 

         WHERE hire_date < '30-DEC-94';

-- add '_SR' to the job_id of the most senior employees

     FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS

       UPDATE emp_temp SET job_id = job_id || '_SR' 

          WHERE emp_sr(i) = emp_temp.employee_id;

-- If any errors occurred during the FORALL SAVE EXCEPTIONS,

-- a single exception is raised when the statement completes.

EXCEPTION

  WHEN dml_errors THEN -- Now we figure out what failed and why.

   errors := SQL%BULK_EXCEPTIONS.COUNT;

   DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);

   FOR i IN 1..errors LOOP

      DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '|| 

         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 

          DBMS_OUTPUT.PUT_LINE('Error message is ' ||

          SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

   END LOOP;

END;

/

DROP TABLE emp_temp;

The output from the example is similar to:

Number of statements that failed: 2

Error #1 occurred during iteration #7

Error message is ORA-12899: value too large for column

Error #2 occurred during iteration #13

Error message is ORA-12899: value too large for column

In Example 11-9, PL/SQL raises predefined exceptions because updated values were too large to insert into the job_id column. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returned 2, and the contents of SQL%BULK_EXCEPTIONS were (7,12899) and (13,12899).

To get the Oracle error message (which includes the code), the value of SQL%BULK_EXCEPTIONS(i).ERROR_CODE was negated and then passed to the error-reporting function SQLERRM, which expects a negative number.

Exceptions - PL/SQL Run time Errors:

In PL/SQL, an error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.

You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions

Because reliability is crucial for database programs, use both error checking and exception handling to ensure your program can handle all possibilities:

• Add exception handlers whenever there is any possibility of an error occurring. Errors are especially likely during arithmetic calculations, string manipulation, and database operations. 
• Add error-checking code whenever you can predict that an error might occur if your code gets bad input data. Expect that at some time, your code will be passed incorrect or null parameters, that your queries will return no rows or more rows than you expect.
• Make your programs robust enough to work even if the database is not in the state you expect. For example, perhaps a table you query will have columns added or deleted, or their types changed. You can avoid such problems by declaring individual variables with %TYPE qualifiers, and declaring records to hold query results with %ROWTYPE qualifiers.
• Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers. Learn the names and causes of the predefined exceptions. If your database operations might cause particular ORA- errors, associate names with these errors so you can write handlers for them. (You will learn how to do that later in this chapter.)
• Write out debugging information in your exception handlers. You might store such information in a separate table. If so, do it by making a call to a procedure declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you roll back the work that the main procedure was doing.
• Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue. Remember, no matter how severe the error is, you want to leave the database in a consistent state and avoid storing any bad data.

Advantages of PL/SQL Exceptions

Using exceptions for error handling has several advantages. With exceptions, you can reliably handle potential errors from many statements with a single exception handler:

Example  Managing Multiple Errors With a Single Exception Handler

DECLARE
   emp_column       VARCHAR2(30) := 'last_name';
   table_name       VARCHAR2(30) := 'emp';
   temp_var         VARCHAR2(30);
BEGIN
  temp_var := emp_column;
  SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column);
-- processing here
  temp_var := table_name;
  SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
-- processing here
EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors
     DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
/

Instead of checking for an error at every point it might occur, just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.

Sometimes the error is not immediately obvious, and could not be detected until later when you perform calculations using bad data. Again, a single exception handler can trap all division-by-zero errors, bad array subscripts, and so on.

If you need to check for errors at a specific spot, you can enclose a single statement or a group of statements inside its own BEGIN-END block with its own exception handler. You can make the checking as general or as precise as you like.

Isolating error-handling routines makes the rest of the program easier to read and understand.

Exception Overview

There are three categories of exceptions in the world of PL/SQL: internally defined, predefined, and user-defined.

An internally defined exception is one that is raised internally by an Oracle Database process; this kind of exception always has an error code but does not have a name unless it is assigned one by PL/SQL or your own code. An example of an internally defined exception is ORA-00060 (deadlock detected while waiting for resource).

A predefined exception is an internally defined exception that is assigned a name by PL/SQL. Most predefined exceptions are defined in the STANDARD package (a package provided by Oracle Database that defines many common programming elements of the PL/SQL language) and are among the most commonly encountered exceptions. One example is ORA-00001, which is assigned the name DUP_VAL_ON_INDEX in PL/SQL and is raised when a unique index constraint is violated.

A user-defined exception is one you have declared in the declaration section of a program unit. User-defined exceptions can be associated with an internally defined exception (that is, you can give a name to an otherwise unnamed exception) or with an application-specific error.

Every exception has an error code and an error message associated with it. Oracle Database provides functions for retrieving these values when you are handling an exception (see Table ). 

Description	How to Get It
The error code. This code is useful when you need to look up generic information about what might cause such a problem.	SQLCODE Note: You cannot call this function inside a SQL statement.
The error message. This text often contains application-specific data such as the name of the constraint or the column associated with the problem.	SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK Note: You cannot call SQLERRM inside a SQL statement.
The line on which the error occurred. This capability was added in Oracle Database 10g Release 2 and is enormously helpful in tracking down the cause of errors.	DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
The execution call stack. This answers the question “How did I get here?” and shows you the path through your code to the point at which DBMS_UTILITY.FORMAT_CALL_STACK is called.	DBMS_UTILITY.FORMAT_CALL_STACK

Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT

To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time.

In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax




PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);




where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in Example:

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      NULL; -- handle the error
END;
/

Raising Exception

You can, however, raise exceptions in your own code. Why would you want to do this? Because not every error in an application is due to a failure of internal processing in the Oracle Database instance. It is also possible that a certain data condition constitutes an error in your application, in which case you need to stop the processing of your algorithms and, quite likely, notify the user that something is wrong.

PL/SQL offers two mechanisms for raising an exception: 

• The RAISE statement
• The RAISE_APPLICATION_ERROR built-in procedure

The RAISE statement

You can use the RAISE statement to raise a user-defined exception or an Oracle Database predefined exception. In the following example, I have decided that if the user has supplied a NULL value for the department ID, I will raise the VALUE_ERROR exception: 

CREATE OR REPLACE PROCEDURE 
process_department (
   department_id_in IN INTEGER)
IS
BEGIN
   IF department_id_in IS NULL
   THEN
      RAISE VALUE_ERROR;
   END IF;
 

You can also use RAISE to reraise an exception from within the exception section.

RAISE_APPLICATION_ERROR:

If you need to pass an application-specific message back to your users when an error occurs, you should call the RAISE_APPLICATION_ERROR built-in procedure. This procedure accepts an integer (your error code), whose value must be between -20,999 and -20,000, and a string (your error message).

When this procedure is run, execution of the current PL/SQL block halts immediately and an exception (whose error code and message are set from the values passed to RAISE_APPLICATION_ERROR  is raised. Subsequent calls to SQLCODE and SQLERRM will return these values.

Here is an example of using RAISE_APPLICATION_ERROR  An employee must be at least 18 years old. If the date of birth is more recent, raise an error so that the INSERT or UPDATE is halted, and pass back a message to the user: 

CREATE OR REPLACE PROCEDURE

validate_employee (

birthdate_in IN DATE)

IS

BEGIN

IF birthdate_in >

ADD_MONTHS (SYSDATE, -12 * 18)

THEN

RAISE_APPLICATION_ERROR (-20500

, 'Employee must be at least

18 years old.');

END IF;

END;

Scope Rules for PL/SQL Exceptions

You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks.

Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.

If you redeclare a global exception in a sub-block, the local declaration prevails. The sub-block cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label:

block_label.exception_name

Example Scope of PL/SQL Exceptions

DECLARE
   past_due EXCEPTION;
   acct_num NUMBER;
BEGIN
   DECLARE  ---------- sub-block begins
      past_due EXCEPTION;  -- this declaration prevails
      acct_num NUMBER;
     due_date DATE := SYSDATE - 1;
     todays_date DATE := SYSDATE;
   BEGIN
      IF due_date < todays_date THEN
         RAISE past_due;  -- this is not handled
      END IF;
   END;  ------------- sub-block ends
EXCEPTION
  WHEN past_due THEN  -- does not handle raised exception
    DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/

Redeclaring Predefined Exceptions

Remember, PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself. Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. For example, if you declare an exception named invalid_number and then PL/SQL raises the predefined exception INVALID_NUMBER internally, a handler written for INVALID_NUMBER will not catch the internal exception. In such cases, you must use dot notation to specify the predefined exception, as follows:

EXCEPTION
  WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
    -- handle the error
END;

Continuing after an Exception Is Raised

An exception handler lets you recover from an otherwise fatal error before exiting a block. But when the handler completes, the block is terminated. You cannot return to the current block from an exception handler. In the following example, if the SELECT INTO statement raises ZERO_DIVIDE, you cannot resume with the INSERT statement:

CREATE TABLE employees_temp AS 
  SELECT employee_id, salary, commission_pct FROM employees;

DECLARE
  sal_calc NUMBER(8,2);
BEGIN
  INSERT INTO employees_temp VALUES (301, 2500, 0);
  SELECT salary / commission_pct INTO sal_calc FROM employees_temp
    WHERE employee_id = 301;
  INSERT INTO employees_temp VALUES (302, sal_calc/100, .1);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    NULL;
END;
/

You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own sub-block with its own exception handlers. If an error occurs in the sub-block, a local handler can catch the exception. When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends, as shown in Example.

Example  Continuing After an Exception

DECLARE
  sal_calc NUMBER(8,2);
BEGIN
  INSERT INTO employees_temp VALUES (303, 2500, 0);
  BEGIN -- sub-block begins
    SELECT salary / commission_pct INTO sal_calc FROM employees_temp
      WHERE employee_id = 301;
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
        sal_calc := 2500;
  END; -- sub-block ends
  INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    NULL;
END;
/

In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to 2500. Execution of the handler is complete, so the sub-block terminates, and execution continues with the INSERT statement

Retrying a Transaction

After an exception is raised, rather than abandon your transaction, you might want to retry it. The technique is:

1. Encase the transaction in a sub-block.
2. Place the sub-block inside a loop that repeats the transaction.
3. Before starting the transaction, mark a savepoint. If the transaction succeeds, commit, then exit from the loop. If the transaction fails, control transfers to the exception handler, where you roll back to the savepoint undoing any changes, then try to fix the problem.

In Example 10-13, the INSERT statement might raise an exception because of a duplicate value in a unique column. In that case, we change the value that needs to be unique and continue with the next loop iteration. If the INSERT succeeds, we exit from the loop immediately. With this technique, you should use aFOR or WHILE loop to limit the number of attempts.

Example 10-13 Retrying a Transaction After an Exception

CREATE TABLE results ( res_name VARCHAR(20), res_answer VARCHAR2(3) );
CREATE UNIQUE INDEX res_name_ix ON results (res_name);
INSERT INTO results VALUES ('SMYTHE', 'YES');
INSERT INTO results VALUES ('JONES', 'NO');

DECLARE
   name     VARCHAR2(20) := 'SMYTHE';
   answer   VARCHAR2(3) := 'NO';
   suffix   NUMBER := 1;
BEGIN
   FOR i IN 1..5 LOOP  -- try 5 times
      BEGIN  -- sub-block begins
         SAVEPOINT start_transaction;  -- mark a savepoint
         /* Remove rows from a table of survey results. */
         DELETE FROM results WHERE res_answer = 'NO';
         /* Add a survey respondent's name and answers. */
         INSERT INTO results VALUES (name, answer);
 -- raises DUP_VAL_ON_INDEX if two respondents have the same name
         COMMIT;
         EXIT;
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN
            ROLLBACK TO start_transaction;  -- undo changes
            suffix := suffix + 1;           -- try to fix problem
            name := name || TO_CHAR(suffix);
      END;  -- sub-block ends
   END LOOP;
END;
/

Re-raising exceptions

You could simply record information about an error and then not reraise the exception. The problem with this approach is that your application has “swallowed up” an error. The user (or the script that is being run) will not know that there was a problem. In some scenarios, that may be OK, but they are very rare. In almost every situation when an error occurs, you really do want to make sure that the person or the job running the code that raised the error is informed.

Oracle Database makes it easy to do this with the RAISE statement. If you use RAISE in an executable section, you must specify the exception you are raising, as in  

RAISE NO_DATA_FOUND;

But inside an exception handler, you can also use RAISE without any exception, as in 

RAISE;

In this form, Oracle Database will reraise the current exception and propagate it out of the exception section to the enclosing block.

Note that if you try to use RAISE outside of an exception section, Oracle Database will raise a compile-time error: 

PLS-00367: a RAISE statement with no exception name must be inside an exception handler

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions:

Exception	Oracle Error	SQLCODE	Description
ACCESS_INTO_NULL	06530	-6530	It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND	06592	-6592	It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	06531	-6531	It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX	00001	-1	It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR	01001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	01722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED	01017	-1017	It is raised when s program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND	01403	+100	It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON	01012	-1012	It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR	06501	-6501	It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH	06504	-6504	It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL	30625	-30625	It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR	06500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	01422	-1422	It is raised when s SELECT INTO statement returns more than one row.
VALUE_ERROR	06502	-6502	It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE	01476	1476	It is raised when an attempt is made to divide a number by zero.