Oracle PL/SQL Tutorial/PL SQL Programming/Error

Display error

   <source lang="sql">

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></source>

Get error information: SQLCODE, SQLERRM

   <source lang="sql">

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></source>