Forum Stats

  • 3,817,240 Users
  • 2,259,294 Discussions
  • 7,893,710 Comments

Discussions

PLS-00103: Encountered the symbol "end-of-file" in trigger

User_R7LP2
User_R7LP2 Member Posts: 1 Green Ribbon

I got the message Error at line 31: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

This is my trigger:

create or replace TRIGGER add_fiscaly

AFTER INSERT ON MDW_SCRAP_MDCOPY

FOR EACH ROW

BEGIN


    SELECT PR_YEAR||PR_MONTH

    INTO :NEW.Fiscaly

    from

        (SELECT

            CASE EXTRACT (MONTH FROM SYSDATE)WHEN 1 THEN EXTRACT(YEAR FROM SYSDATE)-1 

                WHEN 2 THEN EXTRACT(YEAR FROM SYSDATE)-1

                WHEN 3 THEN EXTRACT(YEAR FROM SYSDATE)-1

                ELSE EXTRACT(YEAR FROM SYSDATE)

            END

            AS PR_YEAR  , 


            CASE EXTRACT (MONTH FROM SYSDATE)

                WHEN 04 THEN 1

                WHEN 05 THEN 1

                WHEN 06 THEN 1

                WHEN 07 THEN 1

                WHEN 08 THEN 1

                WHEN 09 THEN 1

                WHEN 10 THEN 2

                WHEN 11 THEN 2

                WHEN 12 THEN 2

                WHEN 01 THEN 2

                WHEN 02 THEN 2

                WHEN 03 THEN 2

            END 

            AS PR_MONTH

        FROM dual)

END;

/


The line 31 is: AS PR_MONTH

I'd appreciate your help

Tagged:

Answers

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 173 Gold Badge

    Start with adding a ; after the last FROM dual)

    ending the select into ... sentence

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 173 Gold Badge
    edited Mar 6, 2021 11:17AM

    And I think your determinination of the fiscal period can be done with less code :

    check the example


    DECLARE

    PROCEDURE GET_FISCALY( p_date DATE := sysdate)

    IS

     new_fiscaly varchar2(10);

     new_date DATE;

    BEGIN

       new_date := ADD_MONTHS(p_date,-3);

       new_fiscaly := to_char(new_date,'YYYY') || ROUND(TO_NUMBER(TO_CHAR(new_date,'Q'))/2);

    dbms_output.put_line(to_char(p_date, 'DD/MM/YYYY') || ':' ||new_fiscaly);

    END;

    begin

     get_fiscaly;

     FOR i IN  1 .. 60 LOOP

       get_fiscaly(ADD_MONTHS(sysdate,i));

     END LOOP;   

    end;

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,930 Red Diamond

    Hi, @User_R7LP2

    The line 31 is: AS PR_MONTH

    It would be logical to think that, but actually that's line 28. In triggers, the first BEGIN or DECLARE statement is considered line 1.

    Another tricky thing about error messages is that the line number reported is the line where the error was detected. For syntax errors, that can be later than the actual error. For example, if you omit the closing single-quote of a string literal, the error may not be detected for several lines. In this case, the actual error (as Hub said) seems to be missing a semicolon after     

    FROM dual)
    

    which is actually line 29, but the error wasn't detected until

    /
    

    which is line 31.

  • mathguy
    mathguy Member Posts: 10,490 Blue Diamond

    And, to fully clarify the error: How is the parser reading your code? Answer: at the end of your subquery (the closing parenthesis), there is no semicolon; there is the word END, where you might otherwise have an alias for the subquery. The parser accepts that without asking questions (why would it?) It thinks END is the alias for the subquery; then there is a semicolon after END. The parser thinks that is the end of the SELECT ... INTO ... statement. Up to that point THERE IS NO ERROR.

    Then it comes straight to the slash terminating the code for the trigger. But wait - where is the END; command? You may think it's there, but it isn't; from the parser's point of view, the END in your code is an alias, and the semicolon after it ends the SELECT ... INTO ... statement - and there is no END; command in the block.