3 Top PL/SQL Errors and How to Fix Them

Runtime errors come due to design/coding faults. PL/SQL is strongly built, like any other language, 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 the procedure, we can handle runtime errors. These are just 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 to more here.

Related articles

• PL/SQL – Create Trigger (srinimf.com)
• PL/SQL – Difficult Questions (srinimf.com)
• PL/SQL – Complex Queries (Job Interviews) (srinimf.com)
• PL/SQL Tutorial: Expanding the Value of SQL (udemy.com)