Executing anonymous PL/SQL block

Question

The question is asking for an anonymous PL/SQL block that:
declares a variable that counts all products in Products table
has an if-else statement depending on whether count is >= or < 7

My try so far:

SET SERVEROUTPUT ON;
DECLARE
  number_of_products NUMBER;
BEGIN
  SELECT SUM(product_id)
  INTO number_of_products
  FROM products
  IF number_of_products >= 7 THEN --Syntax error on this line, not sure why
    DBMS_OUTPUT.PUT_LINE('The number of products is greater than or equal to 7');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The number of products is less than 7');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred');
END;

Error messages:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 3:
PLS-00103: Encountered the symbol "ELSE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "EXCEPTION"
06550. 00000 - "line %s, column %s:\n%s"

What am I missing?

@Zeus might be right, and also, don't use WHEN OTHERS THEN then make it output using DBMS_OUTPUT.PUT_LINE. its bad practice. — Migs Isip

Zeus Zeus · Accepted Answer · 2017-04-06T20:50:15

SET SERVEROUTPUT ON;
DECLARE
  number_of_products NUMBER;
BEGIN
  SELECT SUM(product_id)
  INTO number_of_products
  FROM products;
  IF number_of_products >= 7 THEN --Syntax error on this line, not sure why
    DBMS_OUTPUT.PUT_LINE('The number of products is greater than or equal to 7');
  ELSE
    DBMS_OUTPUT.PUT_LINE('The number of products is less than 7');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred');
END;

Executing anonymous PL/SQL block

1 Answers