CODEWITHSUNDEEP
sqlplsql
SummerBoy
SummerBoy

Reputation: 125

Executing anonymous PL/SQL block

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?

Upvotes: 1

Views: 3772

Answers (1)

Zeus
Zeus

Reputation: 6576

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;

Upvotes: 2

Related Questions