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.

Have Something to Say? Post Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s