PL/SQL- Expected Errors in Executing and Compiling

Runtime errors come due to design/coding fault. PL/SQL strongly built , like any other languages, to handle errors.

Join in the Leading Free Tech Forum SriniForum to discuss more

Different kinds of errors:

- Runtime errors: These will come when executing a procedure.

By writing procedure, we can handle runtime errors. These are just an examples. See Oracle docs for more.

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;

- Compilation errors:

  1. SEVERE: Messages for conditions that might cause unexpected behavior or wrong results.
  2. PERFORMANCE: Messages for conditions that might cause performance problems, such as passing a VARCHAR2 value to a NUMBER column in an INSERT statement.
  3. 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.

- User defined raise errors:

See the example code how we write

BEGIN
   IF number_on_hand < 1 THEN
      RAISE out_of_stock; — raise an exception that we defined
   END IF;

Refer more here.