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;
Advertisement
Answer
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;