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

User_R7LP2

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

Hub Tijhuis

Start with adding a ; after the last FROM dual)

ending the select into ... sentence

Hub Tijhuis

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

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

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.