CREATE TABLE at_test ( id NUMBER NOT NULL, description VARCHAR2(50) NOT NULL ); INSERT INTO at_test (id, description) VALUES (1, 'Description for 1'); INSERT INTO at_test (id, description) VALUES (2, 'Description for 2'); SELECT * FROM at_test; ID DESCRIPTION ---------- -------------------------------------------------- 1 Description for 1 2 Description for 2 2 rows selected. SQL> Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement. DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN FOR i IN 3 .. 10 LOOP INSERT INTO at_test (id, description) VALUES (i, 'Description for ' || i); END LOOP; COMMIT; END; / PL/SQL procedure successfully completed. SELECT * FROM at_test; ID DESCRIPTION ---------- -------------------------------------------------- 1 Description for 1 2 Description for 2 3 Description for 3 4 Description for 4 5 Description for 5 6 Description for 6 7 Description for 7 8 Description for 8 9 Description for 9 10 Description for 10 10 rows selected. SQL> As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result. ROLLBACK; SELECT * FROM at_test; ID DESCRIPTION ---------- -------------------------------------------------- 3 Description for 3 4 Description for 4 5 Description for 5 6 Description for 6 7 Description for 7 8 Description for 8 9 Description for 9 10 Description for 10 8 rows selected. SQL>
CREATE TABLE error_logs ( id NUMBER(10) NOT NULL, log_timestamp TIMESTAMP NOT NULL, error_message VARCHAR2(4000), CONSTRAINT error_logs_pk PRIMARY KEY (id) ); CREATE SEQUENCE error_logs_seq; We define a procedure to log error messages as an autonomous transaction. CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO error_logs (id, log_timestamp, error_message) VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message); COMMIT; END; / The following code forces an error, which is trapped and logged. BEGIN INSERT INTO at_test (id, description) VALUES (998, 'Description for 998'); -- Force invalid insert. INSERT INTO at_test (id, description) VALUES (999, NULL); EXCEPTION WHEN OTHERS THEN log_errors (p_error_message => SQLERRM); ROLLBACK; END; / PL/SQL procedure successfully completed. SELECT * FROM at_test WHERE id >= 998; no rows selected SELECT * FROM error_logs; ID LOG_TIMESTAMP ---------- --------------------------------------------------------------------------- ERROR_MESSAGE ---------------------------------------------------------------------------------------------------- 1 28-FEB-2006 11:10:10.107625 ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION") 1 row selected. SQL>