Oracle PL/SQL Tutorial/PL SQL Programming/Error

Display error

SQL>
SQL> set serveroutput on
SQL>
SQL> create or replace procedure proc0 is
  2  begin
  3     RAISE no_data_found;
  4  end;
  5  /
Procedure created.
SQL>
SQL>
SQL> create or replace procedure proc1 is
  2  begin
  3     NULL;
  4     dbms_output.put_line ("calling proc0");
  5     proc0;
  6  EXCEPTION
  7     WHEN OTHERS THEN RAISE DUP_VAL_ON_INDEX;
  8  end;
  9  /
Procedure created.
SQL>
SQL> create or replace procedure proc2 is
  2  begin
  3     dbms_output.put_line ("calling proc1");
  4     proc1;
  5  EXCEPTION
  6     WHEN OTHERS THEN RAISE value_error;
  7  end;
  8  /
Procedure created.
SQL>
SQL>
SQL> create or replace procedure proc3 is
  2  begin
  3     dbms_output.put_line ("calling proc2");
  4     proc2;
  5  end;
  6  /
Procedure created.
SQL>
SQL>
SQL> BEGIN
  2     proc3;
  3  END;
  4  /
calling proc2
calling proc1
calling proc0
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "sqle.PROC2", line 6
ORA-00001: unique constraint (.) violated
ORA-06512: at "sqle.PROC1", line 7
ORA-01403: no data found
ORA-06512: at "sqle.PROC3", line 4
ORA-06512: at line 2

SQL>

Get error information: SQLCODE, SQLERRM

SQL>
SQL> CREATE OR REPLACE PROCEDURE getErrorInfo (
  2     errcode OUT INTEGER,
  3     errtext OUT VARCHAR2)
  4  IS
  5     c_keyword CONSTANT CHAR(23) := "java.sql.SQLException: ";
  6     c_keyword_len CONSTANT PLS_INTEGER := 23;
  7     v_keyword_loc PLS_INTEGER;
  8     v_msg VARCHAR2(1000) := SQLERRM;
  9  BEGIN
 10     v_keyword_loc := INSTR (v_msg, c_keyword);
 11     IF v_keyword_loc = 0
 12     THEN
 13        errcode := SQLCODE;
 14        errtext := SQLERRM;
 15     ELSE
 16        errtext := SUBSTR (
 17           v_msg, v_keyword_loc + c_keyword_len);
 18        errcode :=
 19           SUBSTR (errtext, 4, 6 /* ORA-NNNNN */);
 20     END IF;
 21  END;
 22  /
SP2-0804: Procedure created with compilation warnings
SQL>
SQL>
SQL> show error
Errors for PROCEDURE GETERRORINFO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PLW-07203: parameter "ERRTEXT" may benefit from use of the NOCOPY
         compiler hint
SQL>
SQL>