How to add exception for ORA-06512, ORA-04088 errors in PL SQL?-CodePudding

So, I'm working on this project and I added the following trigger to check age eligibility.

create or replace TRIGGER AGEVALIDATION 
BEFORE INSERT OR UPDATE ON RECIPIENT 
FOR EACH ROW
BEGIN
  IF (EXTRACT (YEAR FROM SYSDATE) - EXTRACT (YEAR FROM :NEW.DATE_OF_BIRTH)) < 12 THEN
  raise_application_error(-20001,'VACCINE ELIGIBILITY AGE IS 12 AND ABOVE');
  end if;
END;

The following trigger works but needs to handle the following errors. I need both ORA-06512 and ORA-6512 handled. Can anyone help me with this?

Error starting at line : 1 in command -
INSERT INTO RECIPIENT(RECIPIENT_ID,FIRST_NAME,LAST_NAME,DATE_OF_BIRTH,CONTACT_NUMBER,STREET_ADDRESS,CITY,ZIPCODE,GENDER)
VALUES(152,'Batman1','adams','23-OCT-2019',6172544372,'234 HUNTINGTON AVE','BOSTON','02115','MALE')
Error report -
ORA-20001: VACCINE ELIGIBILITY AGE IS 12 AND ABOVE
ORA-06512: at "APP_ADMIN.AGEVALIDATION", line 3
ORA-04088: error during execution of trigger 'APP_ADMIN.AGEVALIDATION'

CodePudding user response：

The trigger does not quite do what you think as you can compare 2022-01-01 and 2010-12-31 and the difference between 2022 and 2010 is 12 years but between the dates is only 11 years and 1 day. You need to use MONTHS_BETWEEN to compare the entire date:

create or replace TRIGGER AGEVALIDATION 
  BEFORE INSERT OR UPDATE ON RECIPIENT 
  FOR EACH ROW
BEGIN
  IF MONTHS_BETWEEN(SYSDATE, :NEW.DATE_OF_BIRTH) < 12*12 THEN
    raise_application_error(-20001,'VACCINE ELIGIBILITY AGE IS 12 AND ABOVE');
  END IF;
END;
/

fiddle

I need both ORA-06512 and ORA-6512 handled.

The trigger cannot "handle" those exceptions as nothing in the trigger would generate those exceptions. What you are probably seeing is part of the stack trace that is generated from your user-defined exception and can be ignored; however, you have not included the complete stack-trace in your question so it is difficult to be sure.

If something did need "handling" then you should do it from the code where you perform the INSERT and not in the trigger.

CodePudding user response：

The exception you need to catch is the -20001 which is actually being raised. Something like:

DECLARE
  excpUser_1  EXCEPTION;
  EXCEPTION_INIT(excpUser_1, -20001);
BEGIN
  UPDATE RECIPIENT SET DATE_OF_BIRTH = SYSDATE - 3650;  -- ten years, more or less
EXCEPTION
  WHEN excpUser_1 THEN
    DBMS_OUTPUT.PUT_LINE('Caught excpUser_1');
END;