SQL>
SQL> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_ErrorText myLogTable.message%TYPE;
3 BEGIN
4 /* SQLERRM(0) */
5 v_ErrorText := SUBSTR(SQLERRM(0), 1, 200);
6 INSERT INTO myLogTable (code, message, info)
7 VALUES (0, v_ErrorText, 'SQLERRM(0)');
8
9 /* SQLERRM(100) */
10 v_ErrorText := SUBSTR(SQLERRM(100), 1, 200);
11 INSERT INTO myLogTable (code, message, info)
12 VALUES (100, v_ErrorText, 'SQLERRM(100)');
13
14 /* SQLERRM(10) */
15 v_ErrorText := SUBSTR(SQLERRM(10), 1, 200);
16 INSERT INTO myLogTable (code, message, info)
17 VALUES (10, v_ErrorText, 'SQLERRM(10)');
18
19 /* SQLERRM with no argument */
20 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
21 INSERT INTO myLogTable (code, message, info)
22 VALUES (NULL, v_ErrorText, 'SQLERRM with no argument');
23
24 /* SQLERRM(-1) */
25 v_ErrorText := SUBSTR(SQLERRM(-1), 1, 200);
26 INSERT INTO myLogTable (code, message, info)
27 VALUES (-1, v_ErrorText, 'SQLERRM(-1)');
28
29 /* SQLERRM(-54) */
30 v_ErrorText := SUBSTR(SQLERRM(-54), 1, 200);
31 INSERT INTO myLogTable (code, message, info)
32 VALUES (-54, v_ErrorText, 'SQLERRM(-54)');
33
34 END;
35 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from myLogTable;
CODE MESSAGE
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INFO
----------------------------------------------------------------------------------------------------
0 ORA-0000: normal, successful completion
SQLERRM(0)
100 ORA-01403: no data found
SQLERRM(100)
10 -10: non-ORACLE exception
SQLERRM(10)
CODE MESSAGE
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INFO
----------------------------------------------------------------------------------------------------
ORA-0000: normal, successful completion
SQLERRM with no argument
-1 ORA-00001: unique constraint (.) violated
SQLERRM(-1)
-54 ORA-00054: resource busy and acquire with NOWAIT specified
SQLERRM(-54)
6 rows selected.
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL>