CODEWITHSUNDEEP
oracle-databaseplsql
user1763170
user1763170

Reputation: 117

PLS-00103: Encountered the symbol ";" when expecting one of the following

I'm trying to debug the following code. I'm getting the PLS-00103 twice. Once on line 26 and once on line 35. I've tried commenting out the if statements surrounding the raise no data found exception and the raise statement itself. I can't really see where I'm going wrong. Any help would be greatly appreciated.

   CREATE OR REPLACE PROCEDURE viewingPercentageDif
   (episodeKeyA IN NUMBER, episodeKeyB IN NUMBER)
    IS 
episodeOrderException EXCEPTION;
notConsecutiveException EXCEPTION;

preceedingEpisode NUMBER := 0;
subsequentEpisode NUMBER := 0;
percetageChange NUMBER :=0;

CURSOR episodeCursorA IS SELECT episode.ep_view_figures FROM episode WHERE episode.ep_suragate_pk = episodeKeyA;
CURSOR episodeCursorB IS SELECT episode.ep_view_figures FROM episode WHERE episode.ep_suragate_pk = episodeKeyB;

BEGIN

IF (episodeKeyA > episodeKeyB) THEN
    RAISE episodeOrderException;
END IF;

BEGIN
    OPEN episodeCursorA;
    FETCH episodeCursorA INTO preceedingEpisode;
    IF episodeCursorA%NOTFOUND THEN
        RAISE NO_DATA_FOUND;
    END IF;
    CLOSE;-- line 26
END;

BEGIN
    OPEN episodeCursorB;
    FETCH episodeCursorB INTO preceedingEpisode;
    IF episodeCursorA%NOTFOUND THEN
        RAISE NO_DATA_FOUND;
    END IF;
    CLOSE;-- line 35
END; 

-- this algorythm should be used for consective episodes only
-- need last and first episodes also
IF ((episodeKeyA + 1) = episodeKeyB) THEN
    -- check if preceeding episode viewing figure is > or < subsequent episode
    -- if preceeding episode < subequent then there has been an decrease
    IF preceedingEpisode < subsequentEpisode THEN
        percentageChange := subsequentEpisode - preceedingEpisode;
        percentageChange := (percentageChange / subsequentEpisode) * 100;

            DBMS_OUTPUT.PUT_LINE('Viewing figures have decreased by: ' || percentageChange || '%');

        -- check for increase
    ELSIF preceedingEpisode > subsequentEpisode THEN
            percentageChange := preceedingEpisode - subsequentEpisode;
            percentageChange := (percetageChange / subsequentEpisode) * 100;

            DBMS_OUTPUT.PUT_LINE('Viewing figures have increased by: ' || percentageChange || '%');
    END IF;
ELSE 
RAISE notConsecutiveException;
END IF;

    EXCEPTION 
    WHEN notConsecutiveException THEN
DBMS_OUTPUT.PUT_LINE('These episodes are not consecutive');
    WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
    WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20003, 'AN ERROR WAS ENCOUNTERED');

    END viewingPercentageDif;
    /

Upvotes: 0

Views: 8095

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12159

You are missing the cursor name on your close command:

BEGIN
    OPEN episodeCursorB;
    FETCH episodeCursorB INTO preceedingEpisode;
    IF episodeCursorA%NOTFOUND THEN
        RAISE NO_DATA_FOUND;
    END IF;
    CLOSE;-- line 35
END;

Should be:

BEGIN
    OPEN episodeCursorB;
    FETCH episodeCursorB INTO preceedingEpisode;
    IF episodeCursorA%NOTFOUND THEN
        RAISE NO_DATA_FOUND;
    END IF;
    CLOSE episodeCursorB;
END;

Same for the other cursor. Otherwise, how does Oracle know what cursor you are referring to to close?

See this link for details - Close Cursor Syntax

Upvotes: 4

Related Questions