Is it possible to enter a missing sequence number into another table, on exception in a Trigger

Question

Below is the code which I have written in the exception part of my before insert trigger.

 
CREATE OR REPLACE TRIGGER ORGANIZATIONS_TAB_BI_TRG
    BEFORE INSERT ON ORGANIZATIONS_TAB
    FOR EACH ROW
DECLARE
    -- variables
    lv_CurrentUserID VARCHAR2(30):= NULL;
BEGIN
   -- Getting the current session user id
    SELECT USER 
      INTO lv_CurrentUserID
      FROM DUAL;      
    IF :NEW.ORG_ID IS NULL THEN
      SELECT TO_NUMBER(TO_CHAR(SYSDATE,'RRRR')||LPAD(ORGANIZATIONS_SEQ.NEXTVAL,6,0) ORG_ID
        INTO :NEW.ORG_ID
        FROM DUAL;
    END IF;
EXCEPTION 
  WHEN OTHERS THEN 
        SEQ_AUDIT_PKG.InsertMissingSequence('ORGANIZATIONS_SEQ',
                      ORGANIZATIONS_SEQ.CURRVAL,
                          SYSDATE,
                          SYSDATE,
                          lv_CurrentUserID,
                          'Sequence Missed due to :'||SQLERRM);
END;

Can we do this for storing the missing sequence numbers to keep a track? The

SEQ_AUDIT_PKG.InsertMissingSequence

is as follows:

PROCEDURE InsertMissingSequence(P_SEQ_NAME IN MISSED_SEQ_LOG.SEQUENCE_NAME%TYPE,
                                             P_SEQ_NUMBER IN MISSED_SEQ_LOG.SEQUENCE_NUM%TYPE,
                                             P_SEQ_DT IN MISSED_SEQ_LOG.SEQUENCE_DT%TYPE,
                                             P_CREATION_DT IN MISSED_SEQ_LOG.CREATION_DT%TYPE,
                                             P_CREATED_UID IN MISSED_SEQ_LOG.CREATED_UID%TYPE,
                                             P_REMARKS IN MISSED_SEQ_LOG.REMARKS%TYPE) IS 
  BEGIN
    -- Inserting records into the Missing Sequence Log
    INSERT INTO MISSED_SEQ_LOG (SEQUENCE_NAME,SEQUENCE_NUM,SEQUENCE_DT,CREATION_DT,CREATED_UID,REMARKS)
           VALUES(P_SEQ_NAME,P_SEQ_NUMBER,P_SEQ_DT,P_CREATION_DT,P_CREATED_UID,P_REMARKS);  
  EXCEPTION
    WHEN OTHERS THEN 
      NULL;
  END InsertMissingSequence;

Answer 1

EXCEPTION 
  WHEN OTHERS THEN 
        SEQ_AUDIT_PKG.Inse...

is a bad idea, because 'when others' could be anything. eg, you might have a datafile corruption, or an out of memory error. You dont want to be doing a sequence audit in that respect. Similarly, when that primary trigger fails, you want to be sending that back to the calling program, not telling them that "Hey, all is well" (which is when WHEN-OTHERS typically will do).

You would need your SEQ_AUDIT_PKG.InsertMissingSequence to be an autonomous transaction so that you could log the information outside the transaction scope of the trigger.

But having said all that ..... just dont do it :-)

Sequences have gaps...period. You can

• nocache them
• pin them in the shared pool
• carefully police shutdown abort commands
• capture unused ones with an autonomous transaction

and you'll still eventually get some missing and any promises you've made about no gaps become moot. And the complexity cost toward meeting this goal is just not worth it.