RAISE_APPLICATION_ERROR issue

Question

I have developed a trigger that checks the validity of a date. It works fine because it prevents me from storing an invalid date, but I also get a weird error message and I can't figure out why. My code is the following:

CREATE OR REPLACE TRIGGER  "CHECKDATEVALIDITY" 
BEFORE INSERT OR UPDATE
ON Event
FOR EACH ROW
BEGIN
IF :NEW.day < 1 OR :NEW.month < 1 OR :NEW.month > 12
    THEN
            RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
END IF;

IF :NEW.month = 4 OR :NEW.month = 6 OR :NEW.month = 9 OR :NEW.month = 11 
    THEN
        IF :NEW.day > 30
            THEN
                RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
        END IF;
ELSIF :NEW.month = 2
    THEN
        IF (mod(:NEW.year, 4) = 0)
            THEN
                IF :NEW.day > 29
                    THEN
                        RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
                END IF;
        ELSIF :NEW.day > 28
            THEN
                RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
        END IF;
ELSE
    IF :NEW.day > 31
        THEN
            RAISE_APPLICATION_ERROR(-20101, 'Wrong date');
    END IF;

END IF;

END checkDateValidity;

The error I get is:

error ORA-20101: Wrong date ORA-06512: on "USER587.CHECKDATEVALIDITY", line 28 ORA-04088: error while executing trigger 'USER578.CHECKDATEVALIDITY'.

Also I have noticed that I get the error from the line next to the RAISE_APPLICATION_ERROR invoked. What does issue the error?

Answer 1

What do you consider the "wierd error message"? It looks like a perfectly reasonable stack trace to me. At the bottom of the stack, you got an error executing a trigger. The next line tells you that the error happened at line 28. The top of the stack is your custom error message and number. That all seems quite normal to me (though you appear to have cut off some of the error text associated with the ORA-06512 error)

ORA-20101: Wrong date
ORA-06512: on "USER587.CHECKDATEVALIDITY", line 28
ORA-04088: error while executing trigger 'USER578.CHECKDATEVALIDITY'.

If you're trying to match up the line number, take a look at DBA_SOURCE. For example, this will show you what is on lines 23-32 of your trigger (the offending line +/- 5 lines).

SELECT line, text
  FROM dba_source
 WHERE owner = 'USER578'
   AND name  = 'CHECKDATEVALIDITY'
   AND line BETWEEN 23 and 32;

Of course, I assume this is a classroom exercise and not something you're doing in the real world. In the real world, you'd store in a DATE column and let Oracle take care of ensuring that a valid date was entered.