SQL>
SQL> CREATE TABLE error_log(
2 order_id NUMBER(10) NOT NULL,
3 error_code NUMBER NOT NULL,
4 error_text VARCHAR2(1000) NOT NULL,
5 logged_user VARCHAR2(30) NOT NULL,
6 logged_date DATE NOT NULL
7 );
Table created.
SQL>
SQL> CREATE TABLE order_tab(
2 order_id NUMBER(10) PRIMARY KEY,
3 order_date DATE NOT NULL,
4 total_qty NUMBER,
5 total_price NUMBER(15,2),
6 supp_id NUMBER(6)
7 );
Table created.
SQL>
SQL> INSERT INTO order_tab VALUES (101,sysdate,100,750,1001);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE log_error
2 (p_order_id NUMBER,
3 p_error_code NUMBER,
4 p_error_text VARCHAR2)
5 IS
6 PRAGMA AUTONOMOUS_TRANSACTION;
7 BEGIN
8 INSERT INTO error_log VALUES (p_order_id,p_error_code,p_error_text,USER,SYSDATE);
9 COMMIT;
10 END;
11 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE create_order(p_order_id NUMBER)
2 IS
3 BEGIN
4 INSERT INTO order_tab VALUES(p_order_id,SYSDATE,NULL,NULL,1001);
5 COMMIT;
6 EXCEPTION WHEN OTHERS THEN
7 log_error(p_order_id,SQLCODE,SQLERRM);
8 ROLLBACK;
9 END;
10 /
Procedure created.
SQL>
SQL> DECLARE
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 FUNCTION dummy_log_error RETURN NUMBER;
4 v_num NUMBER(1):=0;
5 returnCode NUMBER :=dummy_log_error;
6 FUNCTION dummy_log_error RETURN NUMBER
7 IS
8 BEGIN
9 INSERT INTO error_log VALUES (-99,-99,'Dummy Error!',USER,SYSDATE);
10 RETURN (-99);
11 END;
12 BEGIN
13 INSERT INTO error_log VALUES (v_num,v_num,'No Error!',USER,SYSDATE);
14 COMMIT;
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table order_tab;
Table dropped.
SQL>
SQL> drop table error_log;
Table dropped.