SQL> DECLARE
2 e_TooManyEmployee EXCEPTION; -- Exception to indicate an error condition
3 v_ErrorCode NUMBER; -- Variable to hold the error message code
4 v_ErrorText VARCHAR2(200); -- Variable to hold the error message text
5
6 BEGIN
7 RAISE e_TooManyEmployee;
8 EXCEPTION
9 WHEN e_TooManyEmployee THEN
10 DBMS_OUTPUT.put_line('e_TooManyEmployee');
11 v_ErrorText := SUBSTR(SQLERRM, 1, 200); -- Note the use of SUBSTR here.
12 DBMS_OUTPUT.put_line(v_ErrorText);
13 /* SQLERRM(0) */
14 v_ErrorText := SUBSTR(SQLERRM(0), 1, 200);
15 DBMS_OUTPUT.put_line(v_ErrorText);
16
17 /* SQLERRM(100) */
18 v_ErrorText := SUBSTR(SQLERRM(100), 1, 200);
19 DBMS_OUTPUT.put_line(v_ErrorText);
20
21 /* SQLERRM(10) */
22 v_ErrorText := SUBSTR(SQLERRM(10), 1, 200);
23 DBMS_OUTPUT.put_line(v_ErrorText);
24
25 /* SQLERRM with no argument */
26 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
27 DBMS_OUTPUT.put_line(v_ErrorText);
28
29 /* SQLERRM(-1) */
30 v_ErrorText := SUBSTR(SQLERRM(-1), 1, 200);
31 DBMS_OUTPUT.put_line(v_ErrorText);
32
33 /* SQLERRM(-54) */
34 v_ErrorText := SUBSTR(SQLERRM(-54), 1, 200);
35 DBMS_OUTPUT.put_line(v_ErrorText);
36
37
38 WHEN OTHERS THEN
39 v_ErrorCode := SQLCODE;
40 END;
41 /
e_TooManyEmployee
User-Defined Exception
ORA-0000: normal, successful completion
ORA-01403: no data found
-10: non-ORACLE exception
User-Defined Exception
ORA-00001: unique constraint (.) violated
ORA-00054: resource busy and acquire with NOWAIT specified
PL/SQL procedure successfully completed.
SQL>