Oracle Error Logging Trigger
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up oracle trigger log up vote 1 down vote favorite 1 I have a trigger which is supposed to test whether an individual punching a time clock has punched their card within the last 5 minutes. If they have not their info should be left to go on to the table clk_data, otherwise a record should be made in the log table utl_logbook and the insert to clk_data should be aborted. When I run a good punch the insert works as advertised. When it is a bad punch (within 5 minutes of the last punch) I get the following error: SQL Error: ORA-20101: Too Soon ORA-06512: at "TRUTRACK.INSERT_CLK_DATA", line 14 ORA-04088: error during execution of trigger 'TRUTRACK.INSERT_CLK_DATA' The transaction is stopped but I get no recording of it in utl_logbook. The trigger code: create or replace TRIGGER "INSERT_CLK_DATA" BEFORE INSERT ON clk_data FOR EACH row BEGIN DECLARE qty INTEGER := 0; BEGIN SELECT COUNT(*) INTO qty FROM clk_data WHERE clk_time BETWEEN (:new.clk_time - 5/(24*60)) AND (:new.clk_time + 5/(24*60)) AND :new.payroll = clk_data.payroll; IF qty > 0 THEN INSERT INTO utl_logbook (time, source, message) VALUES (sysdate, 'INSERT_CLK_DATA', 'Clock punch within restricted window. Payroll ID:' || :new.payroll || ' Time: ' || :new.clk_time || ' Type: ' || :new.type); RAISE_APPLICATION_ERROR(-20101, 'Too Soon'); END IF; END; END; oracle logging triggers share|improve this question edited Oct 9 '12 at 17:25 Nicholas Krasnov 19.2k42849 asked Oct 9 '12 at 17:23 user1732495 813 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted This is can be one of those rare cases when you can employ pragma autonomous_transaction. It will allow you commit without affecting your main transaction. Use it with caution. Find out more about autonomous transactions and autonomous_transaction pragma Here is an example: -- our error logging table create table tb_log( msg varchar2(123) ) / -- our working table create table tb_table( col11 number ) / -- procedure that is going to log errors NK@XE> create or replace procedure log_error(p_msg in varchar2) 2 is 3 pragma autonomous_transaction; 4 begin 5 insert into tb_log(msg) 6 values(p_
TECHNOLOGY: Ask Tom The Trouble with Triggers By Tom Kyte Our technologist looks at trigger maintenance and implementation challenges. Those of you who frequent the asktom.oracle.com Web site know that I have an aversion to triggers. Once upon a time, a long time ago, I thought triggers were the coolest thing ever and I used (and abused) them heavily. Now, whenever possible, I will go very far out of my way to avoid a trigger. I have two primary reasons for avoiding triggers: They cause a long-term maintenance headache. Triggers are tiny bits of code that are not run directly by anything—they just “happen” as a side effect of some other operation. Because their work is done http://stackoverflow.com/questions/12805181/oracle-trigger-log as a side effect, people frequently forget that triggers are there. (And reviewing code for all side effects is difficult, if not impossible.) Most times I see them implemented, they are implemented incorrectly. Triggers contain huge errors in logic that the developer didn’t see or anticipate, usually because that person didn’t expect the problems encountered. Maintenance Headache Hopefully the first reason to avoid triggers, the maintenance headache, is fairly easy to see. Suppose you are working http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html on someone else’s project, because you have inherited someone else’s work. Say you have a bit of code or better yet a stored procedure that represents a transaction, and you read it. You might presume, “I understand what this does. I get it.” But if you are on a system laden with triggers all over the place, you won’t have gotten it at all—you’ll have gotten it wrong, at best. Triggers cause side effects. They do things out of the mainstream. When you run an update and see “1 row processed,” 500 other things may also have happened. More than once, I’ve received an e-mail similar to the following (this is a cut-and-paste—I did not make this up): We have a problem when updating a column. When we update that column (type is varchar2), the update is showing 1,972 rows updated (and we commit after the UPDATE). When we retrieve that column, the query does not return the updated column values, but other columns are being updated and we are able to see those values. What is the problem? I wrote back simply, “Got triggers?” The response back to me was, “Oh, that explains it. We have discovered the issue. We had ‘:new.name := :new.fname || ‘ ’ || :new.lname;’ in the trigger.” (I hear this type of thing week after week. It happens more often
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject OrientedXMLLarge ObjectsTransactionUser http://www.java2s.com/Tutorial/Oracle/0560__Trigger/Recordsaerrorintheerrorloggingtable.htm PrivilegeRecords a error in the error logging table. : Utility trigger«Trigger«Oracle PL/SQL TutorialOracle PL/SQL TutorialTriggerUtility triggerSQL> SQL> SQL> CREATE SEQUENCE system_error_id NOCACHE; http://dbaora.com/dml-error-logging-in-oracle-database-11g-release-2-11-2/ Sequence created. SQL> SQL> CREATE TABLE system_errors 2 (system_error_id NUMBER(10,0), package_name VARCHAR2(50), 3 procedure_name VARCHAR2(50), execution_location varchar2(20), 4 oracle_error_text VARCHAR2(200), 5 additional_information oracle error VARCHAR2(2000), 6 call_stack VARCHAR2(2000), error_stack VARCHAR2(2000), 7 insert_time DATE, insert_user VARCHAR2(30)); Table created. SQL> SQL> COMMENT ON TABLE system_errors IS 2 'Errors generated by stored packages.'; Comment created. SQL> COMMENT ON COLUMN system_errors.system_error_id IS 2 'The system-wide ID to identify a system error. oracle error logging Useful for 3 determining the order in which errors were encountered and 4 logged.'; Comment created. SQL> COMMENT ON COLUMN system_errors.package_name IS 'The package name.'; Comment created. SQL> COMMENT ON COLUMN system_errors.procedure_name IS 'The procedure/function name.'; Comment created. SQL> COMMENT ON COLUMN system_errors.execution_location IS 'A reference to a location in the executing code.'; Comment created. SQL> COMMENT ON COLUMN system_errors.oracle_error_text IS 'The text of the Oracle error message.'; Comment created. SQL> COMMENT ON COLUMN system_errors.additional_information IS 'Any pertinent information the developer may be trapping by the error handler.'; Comment created. SQL> COMMENT ON COLUMN system_errors.call_stack IS 'The call stack at the time of the error.'; Comment created. SQL> COMMENT ON COLUMN system_errors.error_stack IS 'The error stack at the time of the error.'; Comment created. SQL> COMMENT ON COLUMN s
(11.2) Posted on October 7, 2014 by joda3008 This article presents extension for standard DML operations (INSERT, UPDATE, DELETE, MERGE) .. LOG ERRORS INTO. It enables to execute successfully DML operation into target table regardless of errors during processing of rows. Informations about errors are loaded together with rows content into dedicated error table. Syntax Here is general syntax for DML INSERT/UPDATE/DELETE/MERGE ... ... LOG ERRORS [INTO [schema_name.]table_name] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED] where schema_name.table_name - is error table created with DBMS_ERRLOG package simple_expression - is tag that can be applied to failed records. It's stored in error table in column ORA_ERR_TAG$ REJECT LIMIT specifies maximum number of accepted errors before the statment fails and rollback all. Default value is 0 and maximum UNLIMITED Test data To show how it works I need to prepare some test data. --source table CREATE TABLE test_tbl_src ( id1 number, id2 varchar2(10), id3 varchar2(20) ); --target table CREATE TABLE test_tbl_trg ( id1 NUMBER, id2 VARCHAR2(5) NOT NULL, id3 DATE ); --dummy records ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY'; INSERT INTO test_tbl_src VALUES(10, NULL, SYSDATE); INSERT INTO test_tbl_src VALUES(20, 'long name', SYSDATE); INSERT INTO test_tbl_src VALUES(30, 'short', SYSDATE); INSERT INTO test_tbl_src VALUES(40, 'short', '2014.01.01'); INSERT INTO test_tbl_src VALUES(50, 'short', SYSDATE); COMMIT; SELECT * FROM test_tbl_src; ID1 ID2 ID3 ---------- ---------- -------------------- 10 07.10.2014 20 long name 07.10.2014 30 short 07.10.2014 40 short 2014.01.01 50 short 07.10.2014 Package DBMS_ERRLOG Now it's time to prepare error log table for target table TEST_TBL_TRG with package DBMS_ERRLOG. As default error table gets prefix name ERR$_. BEGIN dbms_errlog.create_error_log ( dml_table_name => 'TEST_TBL_TRG' ); END; / New error table ERR$_TEST_TBL_TRG has got extra columns that store informations about encountered errors during DML operation desc test_tbl_trg Name Null Type ---- -------