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

Let me introduce you to a close personal friend: the Oracle PL/SQL documentation. It's online, comprehensive and free. — APC
– APC, Commented Apr 22, 2016 at 7:09

Pavel Zimogorov · Accepted Answer · 2016-04-22 07:08:05Z

1

Try this:

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

answered Apr 22, 2016 at 7:08

Pavel Zimogorov

1,45211 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

skywalker · Accepted Answer · 2016-04-22 07:09:39Z

1

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.

answered Apr 22, 2016 at 7:09

skywalker

8361 gold badge10 silver badges18 bronze badges

Collectives™ on Stack Overflow

ORACLE PL/SQL Programming Trigger problems

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest