CODEWITHSUNDEEP
sqloracle-databasedatabase-trigger
Alex Hainen
Alex Hainen

Reputation: 13

ORACLE SQL TRIGGERS: PLS-00103: Encountered the symbol "(" when expecting one of the following:

Hello I am getting the error PLS-00103: Encountered the symbol "(" when expecting one of the following I cannot find the reason for this error please help me. Thanks

EDIT: correct code pasted

CREATE OR REPLACE TRIGGER "SUNSHINE_DEPT"
BEFORE INSERT ON "PURCHASE"
FOR EACH ROW
DECLARE type VARCHAR2(7);
BEGIN 

    SELECT SERVICETYPE INTO type 
    FROM (
      SELECT P.SERVICETYPE, D.DNAME
      FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO JOIN PURCHASE P ON E.EMPNO = P.SERVEDBY
      WHERE D.DNAME = 'SALES - Sunshine' AND P.SERVEDBY IN (:NEW.SERVEDBY));

    IF type = 'Data Recovery' THEN
      :NEW.AMOUNT := :NEW.AMOUNT * 0.7;
    END IF;
    :NEW.PAYMENTTYPE := 'Cash';
END;
/

Upvotes: 1

Views: 908

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

This code does not look correct:

SELECT SERVICETYPE INTO type
FROM (SELECT P.SERVICETYPE, D.DNAME
      FROM DEPT D JOIN
           EMP E
           ON D.DEPTNO = E.DEPTNO JOIN
           PURCHASE P
           ON E.EMPNO = P.SERVEDBY
      WHERE D.DNAME = 'SALES - Sunshine'
     ) AND P.SERVEDBY IN (:NEW.SERVEDBY);

The ) before the AND should be moved after the last condition.

IN with a column is also suspicious. That would not however result in a compilation error, just unexpected behavior at runtime.

Upvotes: 0

GMB
GMB

Reputation: 222642

Here:

FOR EACH ROW
DECLARE type VARCHAR2(7);
BEGIN

type is a reserved word in Oracle. You would need to use another name for the variable.

In this demo on DB Fiddle, the trigger compiles successfully:

CREATE OR REPLACE TRIGGER "SUNSHINE_DEPT"
BEFORE INSERT ON "PURCHASE"
FOR EACH ROW
DECLARE mytype VARCHAR2(7);
BEGIN 

    SELECT SERVICETYPE INTO mytype 
    FROM (
      SELECT P.SERVICETYPE, D.DNAME
      FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO JOIN PURCHASE P ON E.EMPNO = P.SERVEDBY
      WHERE D.DNAME = 'SALES - Sunshine' AND P.SERVEDBY IN (:NEW.SERVEDBY));

    IF mytype = 'Data Recovery' THEN
      :NEW.AMOUNT := :NEW.AMOUNT * 0.7;
    END IF;
    :NEW.PAYMENTTYPE := 'Cash';
END;
/

Upvotes: 1

Related Questions