ORACLE PL/SQL Programming Trigger problems

Question

I'm trying to figure out a simple ORACLE PL/SQL programming problem and I'm having some difficulties with it.

I have to make a trigger that catches inserts into a table, and if the location attribute of the new tuple getting into that table doesn't exist in the database, I need to throw a warning message and insert that new location into another table.

What I have now so far -

CREATE TRIGGER sightTrigger
    AFTER INSERT ON SIGHTINGS
    FOR EACH ROW
DECLARE 
    ct INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO ct
    FROM SIGHTINGS
    WHERE SIGHTINGS.location <> :NEW.location;
    IF ct > 0 THEN
        RAISE_APPLICATION_ERROR('WARNING SIGN' || :NEW.location ||' does not exist in the database');
        INSERT INTO FEATURES(LOCATION, CLASS, LATITUDE, ...)
        VALUES (:NEW.LOCATION, 'UNKNOWN', ...);
    END IF;
END;

I'm getting an error, "PLS-00306: wrong number of types of arguments in call to 'RAISE_APP_ERROR'. Could somebody tell me what's wrong? thank you

Answer 1

Try this:

RAISE_APPLICATION_ERROR(
       -20001, 
       'WARNING SIGN' || :NEW.location || 'does not     exist in the database'
);

Answer 2

Your RAISE_APPLICATION_ERROR takes two arguments (this is from Oracle docs): where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long.