CODEWITHSUNDEEP
sqloracle-databaseplsql
Morgan Lay
Morgan Lay

Reputation: 29

PLS-00103: Encountered the symbol "=" when expecting one of the following.... in PL/SQL script

create or replace function lstnation (listdisplay in varchar2)
return varchar2 is
    nName varchar2 (1000) default null;
    listD varchar2(1000) default null;
    
    cursor display_nation
    is
            select nation.n_name 
            from nation
            inner join region
            on region.r_regionkey = nation.n_nationkey
            where region.r_regionname = listdisplay;
BEGIN
    open display_nation;
    loop
    fetch display_nation into nName;
        exit when display_nation%notfound;
        IF
           listD := listD || RTRIM(nName)||' , ';
    end loop;
    close display_nation;
    return listD;
end lstnation;
/
DECLARE 
    rKey region.r_regionkey%type;
    rName region.r_name%type;
    nList varchar2(1000);
    cursor outer_block is 
        select region.r_regionkey, region.r_name, lstnation(region.r_name)
        from region;
BEGIN
    open outer_block;
        loop
            fetch outer_block into rKey, rName, nList;
        exit when outer_block%notfound;
        dbms.output.put_line(rkey || ' ' || RTRIM(rName) || ': '|| nList);
         end loop;
         close outer_block;
end;
/

I get two errors, how can I fix it

LINE/COL ERROR

19/12 PLS-00103: Encountered the symbol "=" when expecting one of the following: . ( * @ % & = - + < / > at in is mod remainder not rem then <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

20/2 PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior

Upvotes: 1

Views: 3412

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12179

You can save some coding and efficiency by replacing the cursor loop with the listagg function

    select listagg(rtrim(nation.n_name),',')
    from nation
    inner join region
    on region.r_regionkey = nation.n_nationkey
    where region.r_regionname = listdisplay;

So that will collate all the matching rows, and use whatever delimiter is passed in. One thing to be aware of, you have listD varchar2(1000) so as long as the results from the query are less than 1000, you are OK. If you expect a larger result set, you may need to increase or use a clob.

If for some reason, you still want to use the loop method, then you need to fix your IF statement:

loop
    fetch display_nation into nName;
        exit when display_nation%notfound;
        IF <condition> THEN
           listD := listD || RTRIM(nName)||' , ';
        END IF;
    end loop;

Upvotes: 0

Related Questions