PL/SQL function unknown compilation error

Question

Total beginner to PL/SQL

Suppose i have 2 tables,

One is REGION(R_KEY, R_NAME)

and another one called NATION(N_KEY, N_NAME, N_REGIONKEY) where N_REGIONKEY matches a value in R_KEY

example in REGION we have

╔═══════╦════════════════╗
║ R_KEY ║     R_Name     ║
╠═══════╬════════════════╣
║     0 ║ AFRICA         ║
║     1 ║ SOUTHEAST ASIA ║
║     2 ║ EUROPE         ║
╚═══════╩════════════════╝

and in NATION

╔═══════╦═══════════╦═════════════╗
║ N_KEY ║  N_NAME   ║ N_REGIONKEY ║
╠═══════╬═══════════╬═════════════╣
║     0 ║ INDONESIA ║           1 ║
║     1 ║ LONDON    ║           2 ║
║     2 ║ FRANCE    ║           2 ║
║     3 ║ KENYA     ║           0 ║
╚═══════╩═══════════╩═════════════╝

I want to create a function that can take in a regionName as it's parameter, and it will then list out all the nations in that region.

I have tried

create or replace function LISTNATION(regionName IN region.r_name%type)
return varchar2
IS
result varchar2(500)
begin
    for aRow IN (select r_key, r_name, n_name
                 from nation n, region r
                 where n_regionkey = r_Key
                 and r_key = regionKey)
    loop
        result := result || aRow.name || ', ';
    end loop;
    return result;
end LISTNATION;

but this returned a compilation error

Why does this compilation error happen, and is there anyway that we get notified which part of our code is causing these errors?

Thank you

Answer 1

Missing semi-colon, here:

CREATE OR REPLACE FUNCTION LISTNATION (regionName IN region.r_name%TYPE)
   RETURN VARCHAR2
IS
   result  VARCHAR2 (500);                         --> here
BEGIN
   FOR aRow IN (SELECT r_key, r_name, n_name
                  FROM nation n, region r
                 WHERE     n_regionkey = r_Key
                       AND r_key = regionKey)
   LOOP
      result := result || aRow.name || ', ';
   END LOOP;

   RETURN result;
END LISTNATION;
/

If you get an error, query USER_ERRORS, e.g.

SQL> create or replace function LISTNATION(regionName IN region.r_name%type)
  2  return varchar2
  3  IS
  4  result varchar2(500)
  5  begin
  6      for aRow IN (select r_key, r_name, n_name
  7                   from nation n, region r
  8                   where n_regionkey = r_Key
  9                   and r_key = regionKey)
 10      loop
 11          result := result || aRow.name || ', ';
 12      end loop;
 13      return result;
 14  end LISTNATION;
 15  /

Warning: Function created with compilation errors.

SQL> select * from user_errors where name = 'LISTNATION';

NAME                           TYPE           SEQUENCE       LINE   POSITION
------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
LISTNATION                     FUNCTION              1          5          1
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

   := ; not null default character
The symbol ";" was substituted for "BEGIN" to continue.
ERROR                103


SQL>

Or, if you're using SQL*Plus, show errors does the same (only prettier):

SQL> show err
Errors for FUNCTION LISTNATION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PLS-00103: Encountered the symbol "BEGIN" when expecting one of
         the following:
         := ; not null default character
         The symbol ";" was substituted for "BEGIN" to continue.

SQL>