ORA-06550: line 6, column 31: PLS-00103: Encountered the symbol ")"

when expecting one of the following: (

I don't know how to manage error

create or replace PROCEDURE Get_StarInfo(Stars in out starsin.starname%type, cnt out integer, avg out float)
is
begin
    select starname, count(title), avg(length) into Stars, cnt, avg 
    from starsin, movie
    where movietitle=title
    and movieyear=year
    and starname=Stars
    group by starname;
exception
    when others then 
        cnt := -1;
        avg := -1;
end;

declare 
    Stars starsin.starname%type := 'harrison ford';
    cnt integer:=3;
    avg float:=3.1;
begin
    get_starinfo(Stars,cnt,avg);
end;

CodePudding user response：

As Alex commented, it was a bad idea calling variable or parameter the same as built-in functions. Though, you can make it work but - you'd rather not do it that way.

I don't have your tables so I'm using Scott's sample schema, calculating some values from its emp table based on department number (that's your "stars").

SQL> create or replace PROCEDURE Get_StarInfo(Stars in out number, cnt out integer, avg out float)
  2  is
  3  begin
  4      select deptno, count(*), avg(sal)/20 into Stars, cnt, avg
  5      from emp
  6      where deptno = stars
  7      group by deptno;
  8  exception
  9      when others then
 10          cnt := -1;
 11          avg := -1;
 12  end;
 13  /

Procedure created.

This is how you called it and got the error:

SQL> set serveroutput on;
SQL> declare
  2      Stars number := 10;
  3      cnt integer; -- :=3;
  4      avg float;   -- :=3.1;
  5  begin
  6      get_starinfo(Stars,cnt,avg);
  7      dbms_output.put_line('count = ' || cnt ||', average = ' || "AVG");
  8  end;
  9  /
    get_starinfo(Stars,cnt,avg);
                              *       --> see where the asterisk points to? That's where the error is!
ERROR at line 6:
ORA-06550: line 6, column 31:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
(
ORA-06550: line 8, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable persistable order
overriding static member constructor map


SQL>

Now, how to use that "avg" of yours:

• a side note: cnt and avg local variables (lines #3 and #4) are supposed to accept values returned by the procedure, so there's no point in setting their initial values
• Oracle, by default, uses uppercase for object names, column names etc., so - if you enclose avg into double quotes and switch to uppercase (line #6), there's no error any more

So:

SQL> set serveroutput on;
SQL> declare
  2      Stars number := 10;
  3      cnt integer; -- :=3;
  4      avg float;   -- :=3.1;
  5  begin
  6      get_starinfo(Stars,cnt,"AVG");
  7      dbms_output.put_line('count = ' || cnt ||', average = ' || "AVG");
  8  end;
  9  /
count = 3, average = 145.835

PL/SQL procedure successfully completed.

SQL>

Once again: as suggested, just don't do that. What we usually do, is to use prefix (such as v_ for "variable" or l_ for "local variable" or p_ for "parameter", etc.)