SQL - Encountered the symbol "EXCEPTION" when expecting one of the following

Question

I'm trying to create a procedure. When I run my code I get the error:

21/1     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         ( begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         continue close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe purge

Perhaps it has to do with my BEGIN and END statements? I'm new to SQL and haven't been able to figure it out.

Here's my code:

CREATE OR REPLACE PROCEDURE inserttocasting(idofmovie  IN casting.movieid%TYPE,
                                            idofactor  IN casting.actorid%TYPE,
                                            newordinal IN casting.ordinal%TYPE)
IS
  identical_ordinal EXCEPTION;
  too_many_movies EXCEPTION;
BEGIN

  SELECT cast_year(idofmovie) INTO v_year FROM dual;

  IF ordinal_count(newordinal, idofmovie) >= 1 THEN
    RAISE identical_ordinal;
  ELSIF cast_count(v_year, idofactor) >= 3 THEN
    RAISE too_many_movies;
  ELSE
    INSERT INTO casting (movieid, actorid, ordinal)
    VALUES (idofmovie, idofactor, newordinal);
  END IF;

  EXCEPTION WHEN identical_ordinal THEN
    dbms_output.PUT_LINE('Ordinal already exists for this film!');

  EXCEPTION WHEN too_many_movies THEN
    DBMS_OUTPUT.PUT_LINE('This actor has already been cast in more than three movies this year.');
    COMMIT;
END;
/

Here is the code for the functions being used if necessary:

CREATE OR REPLACE FUNCTION ordinal_count(
newordinal IN CASTING.ORDINAL%TYPE, idofmovie IN CASTING.MOVIEID%TYPE
) RETURN NUMBER AS 
p_ordinal_number NUMBER;
BEGIN
SELECT COUNT(ACTORID)
INTO p_ordinal_number
FROM CASTING
WHERE ORDINAL = newordinal
AND MOVIEID = idofmovie;
RETURN p_ordinal_number;
END ordinal_count;
/
CREATE OR REPLACE FUNCTION cast_year(idofmovie IN CASTING.MOVIEID%TYPE
) RETURN NUMBER AS 
v_year NUMBER;
BEGIN
SELECT YEAR
INTO v_year
FROM MOVIE
WHERE MOVIEID = idofmovie;
RETURN v_year;
END cast_year;
/
CREATE OR REPLACE FUNCTION cast_count(v_year IN MOVIE.YEAR%TYPE, idofactor IN CASTING.ACTORID%TYPE
) RETURN NUMBER AS 
v_count NUMBER;
BEGIN
SELECT COUNT(v_year)
INTO v_count
FROM CASTING
WHERE ACTORID = idofactor;
RETURN v_count;
END cast_count;
/

score 0 · Answer 1 · edited Jan 30 '23 at 23:18

Change your first procedure to:

CREATE OR REPLACE PROCEDURE insertToCasting(
  idofmovie IN CASTING.MOVIEID%TYPE,
  idofactor IN CASTING.ACTORID%TYPE,
  newordinal IN CASTING.ORDINAL%TYPE)
IS
  identical_ordinal EXCEPTION;
  too_many_movies EXCEPTION;
BEGIN
  SELECT cast_year(idofmovie)
    INTO v_year
    FROM dual;

  IF ordinal_count(newordinal, idofmovie) >= 1 THEN
    RAISE identical_ordinal;
  ELSIF cast_count(v_year, idofactor) >= 3 THEN
    RAISE too_many_movies;
  ELSE
    INSERT INTO CASTING (MOVIEID, ACTORID, ORDINAL)
      VALUES (idofmovie, idofactor, newordinal);

    COMMIT;
  END IF;
EXCEPTION
  WHEN identical_ordinal THEN
    DBMS_OUTPUT.PUT_LINE('Ordinal already exists for this film!');
  WHEN too_many_movies THEN
    DBMS_OUTPUT.PUT_LINE('This actor has already been cast in more than three movies this year.');
END;

You can put multiple WHEN blocks in a single EXCEPTION handler. Also, you had the COMMIT statement at the end of the WHEN too_many_movies... handler, when I suspect you wanted it to follow the INSERT. The way it was positioned it would only have executed if the too_many_movies handler was entered, which didn't seem to make sense.

Thank you very much for the help! This looks much better, except I now receive the error: 10/6 PLS-00201: identifier 'V_YEAR' must be declared Am I not storing my function into v_year properly under the BEGIN statement? — sqlman22, Mar 13 '15 at 00:17
You don't have a variable named `v_year` declared in your `insertToCasting` procedure. Try adding one in the declarations section. In addition, now that I look at it, I suggest replacing `SELECT cast_year(idofmovie) INTO v_year FROM dual` with `v_year := cast_year(idofmovie)` which is both shorter and easier to understand. — Bob Jarvis - Слава Україні, Mar 13 '15 at 01:55

SQL - Encountered the symbol "EXCEPTION" when expecting one of the following

1 Answers1