Database Reference
In-Depth Information
Once again, selecting by date is important for performance, so we will create an index on the
UPDATE_TS
column, the only column.
This time, we made it a
UNIQUE
index, meaning that we will only have one entry with a specific
timestamp. On our error log table, the index was not
UNIQUE
, because we may have multiple errors and
entries in the table at the exact same time.
The
t_appsec_errors_maint
table is for internal use only, so we won't create a view and don't
anticipate ever granting privileges on the table.
Creating an Error Log Management Procedure
Our table management task is instigated by a trigger, but before we can define the trigger, we need to
define our procedure that accomplishes the management task. Our management procedure will be
named
p_appsec_errors_janitor
, and it has no parameters.
We want it to run independently; therefore, we define it with the modifier
PRAGMA
AUTONOMOUS_TRANSACTION
. This allows the procedure to do inserts and deletes and commit the changes,
even if the program that called this transaction does not commit. Without this modifier, if we issue a
commit here, we are asking Oracle database to commit every update, insert, or delete we've made in the
current session. When we're dealing with an error, we specifically want to avoid committing anything
except the insert of an error message in our log and cleanup of old entries. Execute the script in Listing 7-
4 to create the procedure.
Listing 7-4.
Procedure to Manage to Error Log Table,
p_appsec_errors_janitor
CREATE OR REPLACE PROCEDURE appsec.p_appsec_errors_janitor
AS
PRAGMA AUTONOMOUS_TRANSACTION;
m_err_no NUMBER;
m_err_txt VARCHAR2(2000);
BEGIN
INSERT INTO t_appsec_errors_maint ( update_ts ) VALUES ( SYSDATE );
COMMIT;
-- Remove error log entries over 45 days old
DELETE FROM t_appsec_errors WHERE update_ts < ( SYSDATE - 45 );
COMMIT;
INSERT INTO t_appsec_errors
( err_no, err_txt, msg_txt ) VALUES
( 0, 'No Error', 'Success managing log file by Janitor' );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
m_err_no := SQLCODE;
m_err_txt := SQLERRM;
INSERT INTO t_appsec_errors
( err_no, err_txt, msg_txt ) VALUES
( m_err_no, m_err_txt, 'Error managing log file by Janitor' );
COMMIT;
END;
/
