Multiple choice technology databases

Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID; EXCEPTION WHEN STATS_EXITS_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE (‘cannot delete this player, child records exist in PLAYER_BAT_STAT table’); END; What prevents this procedure from being created successfully?

  1. A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. The STATS_EXIST_EXCEPTION has not been declared as a number

  3. The STATS_EXIST_EXCEPTION has not been declared as an exception

  4. Only predefined exceptions are allowed in the EXCEPTION section.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to have knowledge about PL/SQL syntax, specifically about creating stored procedures, and error handling using exceptions.

Option A is incorrect. The procedure does not have any syntax errors; there is no errant comma.

Option B is incorrect. The STATS_EXIST_EXCEPTION is not a number or a variable that needs to be declared. It is a user-defined exception that will be raised in case of an exception with the specified condition.

Option C is correct. The STATS_EXIST_EXCEPTION is not declared as an exception, which will cause an error during compilation of the stored procedure. The correct way of declaring a user-defined exception is as follows:

EXCEPTION
  WHEN  THEN
      -- handle the exception

Option D is incorrect. PL/SQL allows users to define their own exceptions using the EXCEPTION keyword. Predefined exceptions are not the only exceptions allowed in the EXCEPTION section.

Therefore, the correct answer is:

The Answer is: C. The STATS_EXIST_EXCEPTION has not been declared as an exception.

AI explanation

PL/SQL requires any user-defined exception to be declared in the DECLARE section (exception_name EXCEPTION;) before it can be referenced in a WHEN clause of an EXCEPTION section. Here the exception name used in WHEN (STATS_EXIST_EXCEPTION / STATS_EXITS_EXCEPTION in the body — a typo either way) was never declared anywhere in the procedure, so the compiler can't resolve it as a valid exception and the CREATE PROCEDURE fails. It isn't a comma/punctuation problem, isn't a data-type issue, and PL/SQL does allow user-defined exceptions alongside predefined ones — so "not declared as an exception" is the correct root cause.

Keep practicing — related questions