PLS-00103: Encountered the symbol "+" when expecting the following: (

Question

Write a PL/SQL program using WHILE loop for calculating the average of the numbers entered by user. Stop the entry of numbers whenever the user enters the number 0.

I have tried below code in SQL developer in sample scott user. I have tried many variations of trying to get input but after 2 inputs it throws the error.

Sorry if not understood or not posted in a proper way as it is my first post on this site.

DECLARE
  num NUMBER := 0;         -- number entered by the user
  sum NUMBER := 0;         -- sum of all entered numbers
  count NUMBER := 0;       -- count of entered numbers
  average NUMBER := 0;     -- average of entered numbers
BEGIN
  WHILE num != 0 LOOP
    DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
    num := &input_number;  -- Prompt the user to enter a number
  
    IF num != 0 THEN
      sum := sum + num;     -- add the entered number to the sum
      count := count + 1;   -- increment the count of entered numbers
    END IF;
  END LOOP;

  -- Calculate the average
  IF count > 0 THEN
    average := sum / count;
    DBMS_OUTPUT.PUT_LINE('Average: ' || average);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No numbers entered!');
  END IF;
END;

After that getting below error in SQL developer.

old:DECLARE
  num NUMBER := 0;         -- number entered by the user
  sum NUMBER := 0;         -- sum of all entered numbers
  count NUMBER := 0;       -- count of entered numbers
  average NUMBER := 0;     -- average of entered numbers
BEGIN
  WHILE num != 0 LOOP
    DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
    num := &num;  -- Prompt the user to enter a number
  
    IF num != 0 THEN
      sum := sum + num;     -- add the entered number to the sum
      count := count + 1;   -- increment the count of entered numbers
    END IF;
  END LOOP;

  -- Calculate the average
  IF count > 0 THEN
    average := sum / count;
    DBMS_OUTPUT.PUT_LINE('Average: ' || average);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No numbers entered!');
  END IF;
END;
new:DECLARE
  num NUMBER := 0;         -- number entered by the user
  sum NUMBER := 0;         -- sum of all entered numbers
  count NUMBER := 0;       -- count of entered numbers
  average NUMBER := 0;     -- average of entered numbers
BEGIN
  WHILE num != 0 LOOP
    DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
    num := 4;  -- Prompt the user to enter a number
  
    IF num != 0 THEN
      sum := sum + num;     -- add the entered number to the sum
      count := count + 1;   -- increment the count of entered numbers
    END IF;
  END LOOP;

  -- Calculate the average
  IF count > 0 THEN
    average := sum / count;
    DBMS_OUTPUT.PUT_LINE('Average: ' || average);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No numbers entered!');
  END IF;
END;

Error starting at line : 1 in command -
DECLARE
  num NUMBER := 0;         -- number entered by the user
  sum NUMBER := 0;         -- sum of all entered numbers
  count NUMBER := 0;       -- count of entered numbers
  average NUMBER := 0;     -- average of entered numbers
BEGIN
  WHILE num != 0 LOOP
    DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
    num := &num;  -- Prompt the user to enter a number
  
    IF num != 0 THEN
      sum := sum + num;     -- add the entered number to the sum
      count := count + 1;   -- increment the count of entered numbers
    END IF;
  END LOOP;

  -- Calculate the average
  IF count > 0 THEN
    average := sum / count;
    DBMS_OUTPUT.PUT_LINE('Average: ' || average);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No numbers entered!');
  END IF;
END;
Error report -
ORA-06550: line 12, column 18:
PLS-00103: Encountered the symbol "+" when expecting one of the following:

   (
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Alex Poole · Accepted Answer · 2023-05-23T18:33:28.317

You have chosen variable names that clash with PL/SQL keywords, sum and count - those have special meanings.

If you change the names this will work, e.g. prefixing all of the variable names with l_ (for local):

DECLARE
  l_num NUMBER := 0;         -- number entered by the user
  l_sum NUMBER := 0;         -- sum of all entered numbers
  l_count NUMBER := 0;       -- count of entered numbers
  l_average NUMBER := 0;     -- average of entered numbers
BEGIN
  WHILE l_num != 0 LOOP
    DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
    l_num := &input_number;  -- Prompt the user to enter a number
  
    IF l_num != 0 THEN
      l_sum := l_sum + l_num;     -- add the entered number to the sum
      l_count := l_count + 1;   -- increment the count of entered numbers
    END IF;
  END LOOP;

  -- Calculate the average
  IF l_count > 0 THEN
    l_average := l_sum / l_count;
    DBMS_OUTPUT.PUT_LINE('Average: ' || l_average);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No numbers entered!');
  END IF;
END;
/

Or at least it won't error... it won't really do anything. You're declaring l_num as:

DECLARE
  l_num NUMBER := 0;         -- number entered by the user

and then you loop with:

  WHILE l_num != 0 LOOP

So that immediately fails the test and the loop exits without doing anything. If you initialise l_num to, say, -1 then it will go into the loop and use the number you supplied. But it will loop forever.

You can't prompt repeatedly in a PL/SQL block. PL/SQL is not interactive. You are being prompted for the substitution variable value before the PL/SQL is parsed/compiled - that's why you are asked for the value and then it errors.

So SQL Developer sees the & placeholder in the block, prompts you for the value, substitutes it - as you can see in the old/new text - and only then compiles and executes the block.

That's why in your 'new' text you can see:

new:DECLARE
  num NUMBER := 0;         -- number entered by the user
  sum NUMBER := 0;         -- sum of all entered numbers
  count NUMBER := 0;       -- count of entered numbers
  average NUMBER := 0;     -- average of entered numbers
BEGIN
  WHILE num != 0 LOOP
    DBMS_OUTPUT.PUT_LINE('Enter a number (enter 0 to exit): ');
    num := 4;  -- Prompt the user to enter a number
...

The 4 is already set and fixed - it will not prompt you again. And as that is fixed as 4, the loop condition is never met as that is not zero, and it will loop forever, or until it runs out of resources. (Assuming you did serveroutput on to see the results, with this code you'll hit the DBMS_OPUTPUT buffer limit after it's tried to output the "Enter a number" prompt a few thousand times.)

You could do something like this but I doubt you're being asked for something that complicated. It's unclear what you're expected to do though. You could prompt for a single list of number but the language used doesn't suggest that's what's required either, and you wouldn't need to end with a zero. So I don't really have a suggestion of how to do what you've been asked for...

Thank you for your help. Actually I am a beginner and was learning some PL/SQL. I saw this one code on [link](https://www.interviewbit.com/pl-sql-interview-questions/) question number 26. I will try out this code soon as I am still learning about SQL Developer environment and trying to get familiar with it. But this is something new I learned from you and I really appreciate the information you have provided. Even ChatGPT couldn't answer it correctly or helped correct the code. — Rohit Raikar, May 26 '23 at 20:27
I had a quick look at that links and several are wrong. Perhaps you should should look for a different resource to learn from *8-) — Alex Poole, May 26 '23 at 21:01

PLS-00103: Encountered the symbol "+" when expecting the following: (

1 Answers1