Failing procedure for testing exception block

Question

I'm testing few procedures which are in production and have added some part in an exception block. I made a copy of those procedure in a test enviorment and I need to raise a exception for testing.

What is a simple way or with a minimum code change, to make procedure enter an exception block?

We just have others in the exception block where we are catching all exceptions. For example:

DECLARE
   -- something
BEGIN
   -- I need some operation to do here which will make control go to exception
EXCEPTION
   WHEN others THEN
      -- handling error (Need to check these changes)
END;

I have created a procedure to test the control flow in case of exception but its giving me error. Code is below

CREATE OR REPLACE Procedure Exception_Check
AS
BEGIN

  dbms_output.put_line('step 1..........');
  raise_application_error(-20111, 'Step 2...........');
  dbms_output.put_line('step 3..........');


EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line('step 4, In to the exception block..........');
  raise_application_error(-20112, 'Step 5........... In raising application error');
END;

What I m doing wrong?

@mari, [this](http://stackoverflow.com/review/suggested-edits/1231025) is _not_ a good suggested edit. Please [do not add random syntax highlighting](http://meta.stackexchange.com/questions/137755/reject-an-already-approved-suggested-edit-when-rolling-it-back); it's unnecessary and makes the post more difficult to read. Only use backticks for code, not for keywords. — Ben, Dec 24 '12 at 10:01
Your procedure is _meant_ to give you an error. That's the entire point of the procedure. [If you look at the output](http://www.sqlfiddle.com/#!4/52205/1) it's error code 20112, which is the error you're raising. — Ben, Dec 24 '12 at 13:01
@Ben - I got it, was little confused with the error messages :) cheers — Pravin Satav, Dec 26 '12 at 10:04

score 4 · Accepted Answer · answered Dec 24 '12 at 09:50

For a start catching everything using EXCEPTION WHEN OTHERS is not necessarily the best practice. If you handle an exception you should know exactly what you're going to do with it. It's unlikely that you have the ability to handle every single Oracle exception properly using OTHERS and if you do so you should probably be logging them somewhere where they'll be noticed.

To quote from Oracle's Guidelines for Avoiding and Handling Exceptions:

Whenever possible, write exception handlers for named exceptions instead of using OTHERS exception handlers.

Learn the names and causes of the predefined exceptions. If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them.

To answer your question you can use the raise_application_error procedure to raise an error at any point in your code:

RAISE_APPLICATION_ERROR (-20000, 'an exception');

The user defined exceptions are those between 20,000 and 20,999 so ensure you use a number in this range.

An example could be:

begin

   raise_application_error(-20500, 'an exception occurred');

exception when others then
   do_something;
end;
/

You can also define your own pre-defined exception, which you can then catch:

declare
   my_exception EXCEPTION;
   PRAGMA EXCEPTION_INIT( my_exception, -20001 );
begin
   raise_application_error( -20001, 'an exception' );
exception when my_exception then
   do_something;
end;

score 2 · Answer 2 · answered Dec 24 '12 at 09:44

2

To raise exception use this code:

raise_application_error(-20111, 'Custom error message');

Oracle documentation: Handling PL/SQL Errors

answered Dec 24 '12 at 09:44

Vasily Komarov

1,405
9
11

This is in my exception block but I need to go to exception block from procedure – Pravin Satav Dec 24 '12 at 09:46
@PravinSatav - no, he means add this to *your procedures* to force them into the exception block. – APC Dec 24 '12 at 09:51
If your insert raise_application_error(-20111, 'Custom error message') after the BEGIN your will go to EXCEPTION block. – Vasily Komarov Dec 24 '12 at 09:54
Thanks. I have created a test proc but I m getting error in it. Can you please tell me what I m doing wrong? I have added procedure in question – Pravin Satav Dec 24 '12 at 11:23
What kind of error? I create and execute it, it's working fine. Here the output: "step 1.......... step 4, In to the exception block..........". – Vasily Komarov Dec 24 '12 at 11:36

Failing procedure for testing exception block

2 Answers2