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;
    /

Answers


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


Need Your Help

Can I detect errors while using a .Net WebBrowser control?

.net winforms com webbrowser-control

I have an .Net Froms application that displays web pages through a WebBrowser control.

jquery with asp.net server button throws an error Invalid postback or callback argument

jquery asp.net-2.0

I am trying to use JQuery in my ASP.Net 2.0 website in this scenario: have three asp:dropdownlist at page. First two are filled at server, third one is filled by JQuery at client side. There is also

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.