Getting PLS-00103 Error. PLS-00103: Encountered the symbol "RETURN"

Question

I am new to PL/sql and I am currently writing some PL/SQL code that has to extract data from two tables. The code that I have so far keeps getting error PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem

This is my code at the moment

CREATE OR REPLACE FUNCTION LISTNATION(N_NAME in VARCHAR2, R_NAME IN VARCHAR2, R_REGIONKEY IN NUMBER)
  2  RETURN VARCHAR2 IS
  3  R_REGIONKEY NUMBER(3);
  4  R_NAME VARCHAR2(50);
  5  N_NAME VARCHAR2(50);
  6  
  7  BEGIN
  8  select r_regionkey, r_name, n_name
  9  from region
 10  inner join nation
 11  on r_regionkey = n_regionkey;
 12  
 13  dbms_output.put_line = (R_REGIONKEY || ' ' || R_NAME || ':' || N_NAME || ',')
 14  
 15  END;
 16  /

Answer 1

The immediate cause of the error you're getting(in the original version of the question) is that you're missing missing a semicolon at the end of line 4.

But there are quite a few other issues:

• you are using double-quotes " around string literals instead of single quotes '. Those are for identifiers, not strings.
• your local variables are the same as the arguments, but you don't need all those arguments anyway.
• you aren't selecting into anything; and your query will return multiple rows.
• your query doesn't have a where clause so it will look for data in all regions.
• you've mangled the dbms_output call; it's missing a closing parenthesis (again, in the original question) and should not have the =.
• you're returning test but haven't declared that.

So, to have the function use dbms_output to display the results - which relies on the caller handling that output, which you shouldn't assume - you could maybe do:

CREATE OR REPLACE FUNCTION LISTNATION(P_R_REGIONKEY IN NUMBER)
RETURN VARCHAR2 IS
  L_R_NAME REGION.R_NAME%TYPE;
  L_N_NAME NATION.N_NAME%TYPE;

BEGIN
  FOR l_row IN (
    select r.r_name, n.n_name
    from region r
    inner join nation n
    on r.r_regionkey = n.n_regionkey
    where r.r_regionkey = p_r_regionkey
  ) LOOP

    dbms_output.put_line (P_R_REGIONKEY || ' ' || l_row.R_NAME || ':' || l_row.N_NAME);

  END LOOP;

  RETURN 'test';
END;
/

That adds a filter to your query, turns it into an implicit cursor, and loops over the results.

db<>fiddle with some made-up data, with table and column names taken from your attempt.

It isn't clear what you actually want to return; you might want a comma-separated list of nation names, in which case look at the listagg() function. For example, you could do something like:

CREATE OR REPLACE FUNCTION LISTNATION(P_R_REGIONKEY IN NUMBER)
RETURN VARCHAR2 IS
  L_RESULT VARCHAR2(4000);

BEGIN
  select listagg(n.n_name, ',') within group (order by n.n_name)
  into l_result
  from region r
  inner join nation n
  on r.r_regionkey = n.n_regionkey
  where r.r_regionkey = p_r_regionkey;

  RETURN l_result;
END;
/

and you could then call that to get back a single list of values.

db<>fiddle

Although using a PL/SQL function wrapper around that query doesn't seem very useful. Presumably this is an exercise though...

---

is there anyway for this to display it such that it only shows the region key and region once while listing all nations?

You can change the second function to include the region info, concatenating that with the listagg result:

CREATE OR REPLACE FUNCTION LISTNATION(P_R_REGIONKEY IN NUMBER)
RETURN VARCHAR2 IS
  L_RESULT VARCHAR2(4000);

BEGIN
  select r.r_regionkey || ' ' || r.r_name || ': '
    || listagg(n.n_name, ',') within group (order by n.n_name)
  into l_result
  from region r
  inner join nation n
  on r.r_regionkey = n.n_regionkey
  where r.r_regionkey = p_r_regionkey
  group by r.r_regionkey, r.r_name;

  RETURN l_result;
END;
/

then call that for each region key you're interested in.

db<>fiddle

The way you've phrased it makes it sound like you don't want to pass in a region key and instead want to see all regions at once; and want to use dbms_output, which isn't ideal; and don't really want to return anything. So you could use a procedure instead, change the cursor query to bring back the region name, and then concatenate in the put_line call inside the loop:

CREATE OR REPLACE PROCEDURE LISTNATION IS
BEGIN
  FOR l_row IN (
    select r.r_regionkey, r.r_name,
      listagg(n.n_name, ',') within group (order by n.n_name) as names
    from region r
    inner join nation n
    on r.r_regionkey = n.n_regionkey
    group by r.r_regionkey, r.r_name
    order by r.r_regionkey
  ) LOOP

    dbms_output.put_line (l_row.R_REGIONKEY || ' ' || l_row.R_NAME || ': ' || l_row.names);

  END LOOP;
END;
/

dbms_output:
1 EMEA: FRANCE,UNITED KINGDOM
2 APAC: CHINA

db<>fiddle

There are lots of variations of course, it depends exactly what you want to have happen, but you should be able to adapt one of these approaches. Another is to have a function or procedure generate a ref cursor, but again it's not clear what you want. But using dbms_output isn't a great idea, as the caller may not be using a client that looks for and displays that.