Search code examples
oracle-databaseplsqltriggers

Another PLS-00103 when writing a trigger

I am writing a trigger for a DB, using the sql developer, but i can't go through this problem:

this is my trigger (a shorter version, but i try to make it work)

create or replace trigger trig_ognisko
        before insert or UPDATE ON podsumowanie
        FOR EACH ROW
  DECLARE
  z1 varchar2;
  z2 varchar2;
  z3 varchar2;
  BEGIN
  IF :NEW.ognisko=(select o.id 
   from ognisko o join grupy_ognisk g on g.id=o.grupa and g.placowka=o.placowka and g.prowadzace!=o.id
   where o.placowka=:NEW.placowka and o.id=:NEW.ognisko)THEN            
    if updating('dzialania_korygujacje_opis') then
      select p.dzialania_korygujacje_opis into z1
      from ognisko o
      join grupy_ognisk g on g.id=o.grupa and g.placowka=o.placowka and g.prowadzace=o.id
      join podsumowanie p on p.ognisko = o.id and p.placowka=o.placowka
      where o.placowka=:NEW.placowka and o.id=:NEW.ognisko;
         IF INSTR(z1,:OLD.dzialania_korygujacje_opis)!=-1 then
           REPLACE(z1,:OLD.dzialania_korygujacje_opis,:NEW.dzialania_korygujacje_opis);
            update podsumowanie pod set pod.dzialania_korygujacje_opis=z1 where
            pod.ID=(
            select p.ID
            from ognisko o
            join grupy_ognisk g on g.id=o.grupa and g.placowka=o.placowka and g.prowadzace=o.id
            join podsumowanie p on p.ognisko = o.id and p.placowka=o.placowka
            where o.placowka=:NEW.placowka and o.id=:NEW.ognisko);
         END IF;
    end if;
  END IF;
END;

After trying to create it, i receive an error: Error(8,23): PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: ) , with group having intersect minus start union where connect

Oracle version 11.2.0.1.0 No idea, what the problem is. I have been searching for a long time!. Maybe you have better google results? DB:

GRUPY_OGNISK:

ID  NUMBER(38,0)    No      1   
PLACOWKA    NUMBER(38,0)    No      2   
USUNIETE    NUMBER(1,0) No      5   
REF_ID  NUMBER(38,0)    Yes     9   
REF_PLACOWKA    NUMBER(38,0)    Yes     10  
REF_TYP NUMBER(38,0)    No      11  
PROWADZACE  NUMBER(38,0)    Yes     13  
PROWADZACE_PLACOWKA NUMBER(38,0)    Yes     14  
PROWADZACE_IDENTYFIKATOR    VARCHAR2(50 CHAR)   Yes     15

OGNISKO:

ID  NUMBER(38,0)    No      1   
PLACOWKA    NUMBER(38,0)    No      2   
REF_ID  NUMBER(38,0)    Yes     9   
REF_PLACOWKA    NUMBER(38,0)    Yes     10  
REF_TYP NUMBER(38,0)    No      11  
GRUPA   NUMBER(38,0)    Yes     38

PODSUMOWANIE:

ID  NUMBER(38,0)    No      1   
PLACOWKA    NUMBER(38,0)    No      2   
USUNIETE    NUMBER(1,0) No      5   
REF_ID  NUMBER(38,0)    Yes     9   
REF_PLACOWKA    NUMBER(38,0)    Yes     10  
REF_TYP NUMBER(38,0)    No      11  
DZIALANIA_KORYGUJACJE_OPIS  VARCHAR2(255 CHAR)  Yes     16  
OGNISKO NUMBER(38,0)    No      26  
OGNISKO_PLACOWKA    NUMBER(38,0)    No      27

Only some of the columns, because there is a shitload of them and they are not important here

Solution

  • The error is in this section:

    IF :NEW.ognisko=(select o.id 
   from ognisko o join grupy_ognisk g on g.id=o.grupa and g.placowka=o.placowka and g.prowadzace!=o.id
   where o.placowka=:NEW.placowka and o.id=:NEW.ognisko)THEN

    You can't use a (sub)query in a PL/SQL condition, you need to select the value into a local variable and compare that. A simplified version of what you are doing is:

    begin
  if 'X' = (select d.dummy from dual d join dual x on x.dummy = d.dummy) then
    null;
  end if;
end;
/

ORA-06550: line 2, column 40:
PLS-00103: Encountered the symbol "JOIN" when expecting one of the following: ...

    It's complaining about the join because that's just where the parser first find a problem it can't resolve; without the join it still errors, with a more meaningful message:

    begin
  if 'X' = (select d.dummy from dual d) then
    null;
  end if;
end;
/

ORA-06550: line 2, column 12:
PLS-00405: subquery not allowed in this context

    You would need to use a local variable, so something like:

    declare
  l_dummy dual.dummy%type;
begin
  select dummy into l_dummy from dual;
  if 'X' = l_dummy then
    null;
  end if;
end;
/

    Or in your case, with a suitable variable defined:

      select o.id into l_id
   from ognisko o join grupy_ognisk g on g.id=o.grupa and g.placowka=o.placowka and g.prowadzace!=o.id
   where o.placowka=:NEW.placowka and o.id=:NEW.ognisko;
  IF :NEW.ognisko=l_id THEN

    But since the select's where condition is doing o.id=:NEW.ognisko then the condition has to be true, unless there is no matching record, in which case you'll get a no-data-found error. If you're testing for existence, do a count instead, and check that, perhaps:

      select count(*) into l_count
   from ognisko o join grupy_ognisk g on g.id=o.grupa and g.placowka=o.placowka and g.prowadzace!=o.id
   where o.placowka=:NEW.placowka and o.id=:NEW.ognisko;
  IF l_count > 0 THEN