Error(8,1): PLS-00103: Encountered the symbol "SET"

Question

I'm trying to create a function using oracle, it should be working fine but i keep getting the following error:

Error(8,1): PLS-00103: Encountered the symbol "SET" here is my Function:

CREATE OR REPLACE FUNCTION CountViewers(nameofPlay plays.play%TYPE) RETURN NUMBER AS
NUM NUMBER;
  BEGIN
   SELECT SUM(registerd) INTO num1 FROM plays WHERE play=nameofPlay;
   return num;
  END;
SET SERVEROUT ON;
DECLARE
inc integer;
res NUMBER;
invalid_status EXCEPTION;
CURSOR clients2 IS SELECT * FROM plays;
name_of_play  plays.play%ROWTYPE;
play_name plays%TYPE;
BEGIN
inc:=0;
name_of_play := '&Play';
OPEN clients2;
LOOP
       FETCH clients2 INTO play_name;
       IF name_of_play != play_name.play and clients2%rowcount>i THEN 
               inc:=inc+1;
       ELSIF name_of_play = play_name.play THEN
               EXIT WHEN clients2%found;
       ELSE
               RAISE invalid_status;
               --like throw error
       END IF;
EXIT WHEN clients2%notfound;
END LOOP;
res:=CountViewers(name_of_play);
DBMS_OUTPUT.PUT_LINE(name_of_play ||'  | '|| res);
EXCEPTION
   WHEN invalid_status THEN DBMS_OUTPUT.PUT_LINE('The name of play is not found');
END;

Does this answer your question? [Error(36,1): PLS-00103: Encountered the symbol "SET"](https://stackoverflow.com/questions/32879339/error36-1-pls-00103-encountered-the-symbol-set) — eshirvana, Jan 14 '21 at 18:11
Did you write all that code, as a beginner, without trying to execute it even once during the process?!? — jarlh, Jan 14 '21 at 18:15
@odaiwa - the immediate problem is covered in one of the answers in the linked question: you're missing a slash (/) between the function creation and the rest of the script. ;See [how to run PL/SQL blocks](https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_four.htm#i1039663) in the documentation, assuming you're using SQL\*Plus, SQL Developer or SQLcl. — Alex Poole, Jan 14 '21 at 18:30

score 1 · Accepted Answer · answered Jan 22 '21 at 05:29

The problem is where do you put this command: SET SERVEROUT ON; cause its inside the function declaration.

You should try to take it outside the function:

SET SERVEROUT ON;
\
CREATE OR REPLACE FUNCTION CountViewers(nameofPlay plays.play%TYPE) RETURN NUMBER AS
NUM NUMBER;
  BEGIN
   SELECT SUM(registerd) INTO num1 FROM plays WHERE play=nameofPlay;
   return num;
  END;
DECLARE
inc integer;
res NUMBER;
invalid_status EXCEPTION;
CURSOR clients2 IS SELECT * FROM plays;
name_of_play  plays.play%ROWTYPE;
play_name plays%TYPE;
BEGIN
inc:=0;
name_of_play := '&Play';
OPEN clients2;
LOOP
       FETCH clients2 INTO play_name;
       IF name_of_play != play_name.play and clients2%rowcount>i THEN 
               inc:=inc+1;
       ELSIF name_of_play = play_name.play THEN
               EXIT WHEN clients2%found;
       ELSE
               RAISE invalid_status;
               --like throw error
       END IF;
EXIT WHEN clients2%notfound;
END LOOP;
res:=CountViewers(name_of_play);
DBMS_OUTPUT.PUT_LINE(name_of_play ||'  | '|| res);
EXCEPTION
   WHEN invalid_status THEN DBMS_OUTPUT.PUT_LINE('The name of play is not found');
END;

Error(8,1): PLS-00103: Encountered the symbol "SET"

1 Answers1