Oracle PL/SQL - Adding Exception Handlers to Your Code

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. Exception

Introduction

Here we use a table to log the error.

Demo

SQL>
SQL>--   www  .  ja va2s.c om
SQL> drop table t_logError;

Table dropped.
SQL>
SQL> create table t_logError(
  2        error_tx VARCHAR2(4000),
  3        date_dt date default sysdate,
  4        loggedby_tx VARCHAR2(32) default user)
  5  /

Table created.
SQL>
SQL>  created.
SP2-0042: unknown command "created." - rest of line ignored.
SQL>
SQL>
SQL> create or replace function f_get_speed_nr(i_Distance_nr NUMBER, i_timeSec_nr NUMBER)
  2   return NUMBER
  3   is
  4      v_out_nr NUMBER;
  5   begin
  6      v_out_nr:= i_distance_nr/i_timeSec_nr;
  7      return v_out_nr;
  8   exception
  9      WHEN ZERO_DIVIDE THEN
 10         insert into t_logError (error_tx)
 11         values ('Divide by zero in the F_GET_SPEED_NR');
 12         return null;
 13  end;
 14  /

Function created.
SQL>
SQL> select * from t_logError;

no rows selected
SQL>
SQL>
SQL> begin
  2       DBMS_OUTPUT.put_line(f_get_speed_nr(3,4));
  3       DBMS_OUTPUT.put_line(f_get_speed_nr(3,0));
  4  end;
  5  /
.75

PL/SQL procedure successfully completed.
SQL>
SQL> drop table t_logError;

Table dropped.
SQL>

Related Topic