bing
Flat 10% & upto 50% off + Free additional Courses. Hurry up!

Handling PL/SQL 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.

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.

Example: Runtime Error Handling

DECLARE

stock_price NUMBER := 9.73;

net_earnings NUMBER := 0;

pe_ratio NUMBER;

BEGIN

-- Calculation might cause division-by-zero error.

pe_ratio := stock_price / net_earnings;

DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);

EXCEPTION -- exception handlers begin

-- Only one of the WHEN blocks is executed.

WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error

DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.');

pe_ratio := NULL;

WHEN OTHERS THEN -- handles all other errors

DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');

pe_ratio := NULL;

END; -- exception handlers and block end here

/

 

Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions        

  • Add exception handlers whenever there is any possibility of an error occurring.
  • Add error-checking code whenever you can predict that an error might occur if your code gets bad input data.
  • Make your programs robust enough to work even if the database is not in the state you expect.
  • Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers.
  • Test your code with different combinations of bad data to see what potential errors arise.
  • Write out debugging information in your exception handlers.

Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue.

 

exceptions

 

Defining Your Own PL/SQL Exceptions

PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements.

 

Declaring PL/SQL Exceptions

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an exception named past_due:

DECLARE

past_due EXCEPTION;

 

Scope Rules for PL/SQL Exceptions

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

 

Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To call RAISE_APPLICATION_ERROR, use the syntax-

raise_application_error(

error_number, message[, {TRUE | FALSE}]);

 

Raising Exceptions with the RAISE Statement

PL/SQL blocks and subprograms should raise an exception only when an error makes it undesirable or impossible to finish processing. You can place RAISE statements for a given exception anywhere within the scope of that exception.

Example: Using RAISE to Force a User-Defined Exception

 DECLARE

out_of_stock EXCEPTION;

number_on_hand NUMBER := 0;

BEGIN

IF number_on_hand < 1 THEN

RAISE out_of_stock; -- raise an exception that we defined

END IF;

EXCEPTION

WHEN out_of_stock THEN

-- handle the error

DBMS_OUTPUT.PUT_LINE('Encountered out-of-stock error.');

END;

/

 

How PL/SQL Exceptions Propagate

When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. If no handler is found, PL/SQL returns an unhandled exception error to the host environment.

 

propagation rules

 

Handling Raised PL/SQL Exceptions

When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part, which is formatted as follows:

EXCEPTION

WHEN exception1 THEN -- handler for exception1

sequence_of_statements1

WHEN exception2 THEN -- another handler for exception2

sequence_of_statements2

...

WHEN OTHERS THEN -- optional handler for all other errors

sequence_of_statements3

END;

 

Overview of PL/SQL Compile-Time Warnings

To make your programs more robust and avoid problems at run time, you can turn on checking for certain warning conditions. These conditions are not serious enough to produce an error and keep you from compiling a subprogram. They might point out something in the subprogram that produces an undefined result or might create a performance problem.

To work with PL/SQL warning messages, you use the PLSQL_WARNINGS initialization parameter, the DBMS_WARNING package, and the USER/DBA/ALL_PLSQL_OBJECT_SETTINGS views.

 

PL/SQL Warning Categories

PL/SQL warning messages are divided into categories, so that you can suppress or display groups of similar warnings during compilation. The categories are:

  • SEVERE: Messages for conditions that might cause unexpected behavior or wrong results, such as aliasing problems with parameters.
  • PERFORMANCE: Messages for conditions that might cause performance problems, such as passing a VARCHAR2 value to a NUMBER column in an INSERT statement.
  • INFORMATIONAL: Messages for conditions that do not have an effect on performance or correctness, but that you might want to change to make the code more maintainable, such as unreachable code that can never be executed.

"0 Responses on Handling PL/SQL Errors"

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.

top

Sales Offer

  • To avail this offer, enroll before 05th December 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer
offer-june

Sign Up or Login to view the Free Handling PL/SQL Errors.