Mega Code Archive
Insert error message to a table in exception handler
<![CDATA[
SQL>
SQL> CREATE TABLE book (
2 isbn CHAR(10) PRIMARY KEY,
3 category VARCHAR2(20),
4 title VARCHAR2(100),
5 num_pages NUMBER,
6 price NUMBER,
7 copyright NUMBER(4),
8 emp1 NUMBER,
9 emp2 NUMBER,
10 emp3 NUMBER
11 );
Table created.
SQL>
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ('1', 'Database', 'Oracle', 563, 39.99, 1999, 1, 2, 3);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2)
2 VALUES ('2', 'Database', 'MySQL', 765, 44.99, 1999, 4, 5);
1 row created.
SQL> INSERT INTO book (isbn, category, title, num_pages, price, copyright, emp1, emp2, emp3)
2 VALUES ('3', 'Database', 'SQL Server', 404, 39.99, 2001, 6, 7, 8);
1 row created.
SQL>
SQL> CREATE TABLE log_table(
2 code VARCHAR2(100),
3 message VARCHAR2(100),
4 info VARCHAR2(100));
Table created.
SQL> DECLARE
2
3 e_Duplicateemp EXCEPTION;
4
5
6 v_emp1 book.emp1%TYPE;
7 v_emp2 book.emp2%TYPE;
8 v_emp3 book.emp3%TYPE;
9
10 BEGIN
11 SELECT emp1, emp2, emp3 INTO v_emp1, v_emp2, v_emp3 FROM book WHERE title = 'XML';
12
13 IF (v_emp1 = v_emp2) OR (v_emp1 = v_emp3) THEN
14 RAISE e_Duplicateemp;
15 END IF;
16 EXCEPTION
17 WHEN e_Duplicateemp THEN
18 INSERT INTO log_table (info) VALUES ('XML has duplicate emp');
19 WHEN OTHERS THEN
20 INSERT INTO log_table (code, message, info) VALUES(NULL, SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 200),'Oracle error occurred');
21 END;
22 /
PL/SQL procedure successfully completed.
SQL> drop table book;
Table dropped.
SQL> drop table log_table;
Table dropped.
]]>