PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "END" to continue

Question

Hi I am just starting to learn PLSQL and i'm currently getting this error but im not sure what is causing the issue.

For my assignment im supposed to create a PLSQL procedure that accepts a user input x to return a list of nations and their respective regions.

R_NAME	N_NAME	COUNT(S_NATIONKEY)
ASIA	INDONESIA	131
ASIA	CHINA	145
MIDDLE EAST	SAUDI ARABIA	132
EUROPE	GERMANY	132

SQL> DECLARE
  2          userInput number;
  3  
  4  PROCEDURE numberOfSupplier(x IN number) IS
  5          -- Declaring variables to be used
  6          r_name region.r_name%type;
  7          n_name nation.n_name%type;
  8  
  9  
 10  BEGIN
 11          -- Print header
 12          dbms_output.put_line(lpad('R_name',25) || lpad('N_Name',25) || lpad('Count(s_nationkey)',25));
 13          dbms_output.put_line('-------------------' || '-------------------' || '-------------------');
 14  
 15          -- Create an implicit cursor to bring in the data
 16  
 17          for Qrow in (
 18              select r_name, n_name, count(s_nationkey) AS cnt
 19                  from region , nation, supplier
 20                  where r_regionkey = n_regionkey
 21                  and n_nationkey = s_nationkey
 22                  group by r_name, n_name
 23                  having count(s_nationkey) > x
 24                  order by r_name)
 25  
 26          -- Loop through query to print to terminal
 27  
 28          loop
 29              dbms_output.put_line(Qrow.r_name || Qrow.n_name || Qrow.cnt);
 30          end loop;
 31  
 32  
 33  END;
 34  
 35  -- Executes Procedure
 36  
 37  BEGIN
 38          userInput := (&MinNumberofsupplier);
 39          numberOfSupplier(userInput);
 40  END;
 41  
 42  /

This is the error message in the terminal

SQL> show errors;
Errors for PROCEDURE NUMBEROFSUPPLIER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
31/2     PLS-00103: Encountered the symbol "END" when expecting one of the
     following:
     := . ( % ;
     The symbol ";" was substituted for "END" to continue.

Answer 1

I'd suggest you to create a stored procedure (instead of an anonymous PL/SQL block) and then call the procedure from anywhere you want.

I removed unnecessary local variable declarations (as you never used them).

SQL> create or replace procedure numberofsupplier(x in number) is
  2  begin
  3    -- Print header
  4    dbms_output.put_line(lpad('R_name',25) || lpad('N_Name',25) || lpad('Count(s_nationkey)',25));
  5    dbms_output.put_line('-------------------' || '-------------------' || '-------------------');
  6
  7    -- Create an implicit cursor to bring in the data
  8    for qrow in (
  9                 select r_name, n_name, count(s_nationkey) as cnt
 10                     from region , nation, supplier
 11                     where r_regionkey = n_regionkey
 12                     and n_nationkey = s_nationkey
 13                     group by r_name, n_name
 14                     having count(s_nationkey) > x
 15                     order by r_name)
 16    -- Loop through query to print to terminal
 17    loop
 18      dbms_output.put_line(qrow.r_name || qrow.n_name || qrow.cnt);
 19    end loop;
 20  end;
 21  /

Procedure created.

Let's call it:

SQL> -- Executes Procedure
SQL> set serveroutput on
SQL> begin
  2    numberofsupplier(&userinput);
  3  end;
  4  /
Enter value for userinput: 1
R_name                   N_Name       Count(s_nationkey)
---------------------------------------------------------

PL/SQL procedure successfully completed.

There's no out put as my tables are empty, but - you should probably see something (using your tables).