PL/SQL compilation syntax error for trigger

February 23, 2023

I have been trying to find the solution to my error in my sql query for a while and can’t seem to find the cause/don’t understand the error.

I’m trying to create a trigger that updates the primary key of a table if one of the main values gets updates (sexe and anneenais(anneenais is year of birth in french))

Here is my code

CREATE OR REPLACE TRIGGER ChevauxUpdateId
BEFORE UPDATE OF anneenais, sexe ON chevaux
FOR EACH ROW
BEGIN
    :NEW.idcheval := TO_CHAR(:NEW.anneenais | :NEW.sexe | SUBSTR(:OLD.idcheval, 6, 5));

    UPDATE proprietede
        SET idcheval = :NEW.idcheval
            WHERE idcheval = :OLD.idcheval;
    
    UPDATE resultats
        SET idcheval = :NEW.idcheval
            WHERE idcheval = :OLD.idcheval;
END;

It gives me the error:

2/86 PLS-00103: Encountered the symbol ")" when expecting one of
the following: . ( * % & | – + / at mod remainder rem => .. <an
exponent (**)> || multiset

11/4 PLS-00103: Encountered the symbol "end-of-file" when
expecting one of the following: end not pragma final instantiable
persistable order overriding static member constructor map

it gives me the error at this line:

:NEW.idcheval := TO_CHAR(:NEW.anneenais | :NEW.sexe | SUBSTR(:OLD.idcheval, 6, 5));

>Solution :

That’s because concatenation operator is two consecutive pipe signs ||, not just one |.

:NEW.idcheval := TO_CHAR(:NEW.anneenais || :NEW.sexe || SUBSTR(:OLD.idcheval, 6, 5));

You could have used concat function, but it gets ugly if there are more than two strings you’re concatenating because it (concat) accepts only two parameters. Then you have to nest them and – as I said – it gets ugly.