Simple common error ORA-06550 in PL/SQL cursor

Question

This is simple PL/SQL block and I have very simple error, ORA-06550 but I can't find it, pls Help me to solve this error..........

Question: Display category wise prize of most costly item

create table menu
(
    item_id number primary key,
    name_ varchar2(20),
    prize number,
    category varchar(15)
);
create table order_
(
    o_id number primary key,
    item_id number references menu(item_id),
    table_no number,
    qty number
);

SQL> declare
  2      cursor data1 is  select max(prize) as "prize_", category
  3                       from menu
  4                       group by category;
  5
  6      cursor data2(pr number, cat varchar(15)) is
  7          select prize, name_, category
  8          from menu
  9          where prize = pr and category = cat;
 10
 11
 12      data1_cat varchar(15);
 13      data1_pri number;
 14
 15      data2_cat varchar(15);
 16      data2_pri number;
 17      data2_name varchar(15);
 18  begin
 19      open data1;
 20      open data2;
 21
 22      fetch data1 into data1_pri, data1_cat;
 23      while data1%found
 24      loop
 25          fetch data2(data1_pri, data1_cat) into data2_pri, data2_name, data2_cat;
 26          while data2%found
 27          loop
 28              dbms_output.put_line(data2_name || ' '  || data2_pri || ' '  || data2_cat);
 29              fetch data2(data1_pri, data1_cat) into data2_pri, data2_name, data2_cat;
 30          end loop;
 31
 32
 33          fetch data1 into data1_pri, data1_cat;
 34      end loop;
 35      close data2;
 36      close data1;
 37  end;
 38  /

Error is blow

ERROR at line 6:
ORA-06550: line 6, column 40:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.
ORA-06550: line 25, column 20:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
. into bulk
ORA-06550: line 25, column 80:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( , % from
ORA-06550: line 29, column 24:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
. into bulk
ORA-06550: line 29, column 84:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( , % from
ORA-06550: line 35, column 5:
PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map

Answer 1

So your anonymous block has way too much mess in it. ORA-06550 is not your problem, the PLS-00103 is and together with ORA-06550 it marks a place where you messed up your code.

First off, your cursor declaration is not according to syntax scheme in documentation. cursor data2(pr number, cat varchar(15)) should be cursor data2(pr number, cat varchar), notice how your first error says ORA-06550: line 6, column 40: PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character, because on line 6 it encountered ( as part of varchar(15) which shouldn't be there.

Your cursor handling is all over the place. Cursor parameters are provided when cursor is being opened not when it is fetched, also you are opening and closing cursor data2 outside of the loop which means that data in the loop are not changed while fetched. Fetch syntax is plain wrong as I mentioned before, no parameters are provided to cursor when data are fetched. This all can be fixed but I would like to propose an cleaner and easier alternative how to call your cursors (unless you have to use open, fetch, close in your assignment):

declare
    cursor data1 is  select max(prize) as "prize_", category
                     from menu
                     group by category;
    cursor data2(pr number, cat varchar) is
        select prize, name_, category
        from menu
        where prize = pr and category = cat;
begin
    for c in data1 loop
        for i in data2(c."prize_", c.category) loop
            dbms_output.put_line(i.name_ || ' '  || i.prize || ' '  || i.category);
        end loop;
    end loop;
end;
/

Here is fixed cursor usage of your original code and I advice you to read more into cursors for example here, I added comments to provide a better look into what is happening in the code:

declare
    cursor data1 is  select max(prize) as "prize_", category
                     from menu
                     group by category;
    cursor data2(pr number, cat varchar) is
        select prize, name_, category
        from menu
        where prize = pr and category = cat;
    data1_cat varchar(15);
    data1_pri number;
    data2_cat varchar(15);
    data2_pri number;
    data2_name varchar(15);
begin
    -- Open cursor data1 and start looping over data in cursor
    open data1;
    loop
        -- Fetch cursor data1 values into variables
        fetch data1 into data1_pri, data1_cat;
        -- Check if cursor provided data, if not exit loop
        exit when data1%notfound;
        -- Open cursor data2 with parameters from variables filled with data from cursor data1 and start looping over data in cursor
        open data2(data1_pri, data1_cat);
        loop
            -- Fetch cursor data2 values into variables
            fetch data2 into data2_pri, data2_name, data2_cat;
            -- Check if cursor provided data, if not exit loop
            exit when data2%notfound;
            -- Handle variables as needed
            dbms_output.put_line(data2_name || ' '  || data2_pri || ' '  || data2_cat);
        end loop;
        -- Close cursor data2 as new cursor will be opened in next loop and without closing it would lead to ORA-06511: PL/SQL: cursor already open
        close data2;
    end loop;
    -- Close cursor data1 as good practice and possibly to avoid ORA-01000: maximum open cursors exceeded
    close data1;
end;
/