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