T.Syk T.Syk - 7 months ago 17
SQL Question

Compile error in sql procedure

table1:

id_client | XY
--------------
01 | str1
02 | str2
03 | str1


table2:

id_client | id_something
-------------------
02 | 32
02 | 48
01 | 32


table3:

id_something | name
--------------------
48 | john
32 | george


I want to write a procedure which takes one of
XY
from table1 values as an argument and gives the
name
from table3 of the most occured
id_something
in table2. I have this code:

CREATE or REPLACE PROCEDURE myprocedure(XYvalue in VARCHAR2(100))
is
cursor countsCursor is select id_something, count(*) count
from table1 join table2 using (id_client)
WHERE XY=XYvalue
group by id_something;
cnt countsCursor%ROWTYPE;
max NUMBER;
idMax table2.id_something%TYPE;
maxName table3.name%TYPE;
BEGIN
max := 0;
open countsCursor;
loop
fetch countsCursor into cnt;
exit when countsCursor%NOTFOUND;

IF (cnt.count > max) THEN
max := cnt.count;
idMax := cnt.id_something;
END IF;

END loop;

select name into maxName from table3 where id_something = idMax;

if (max = 0) THEN
dbms_output.put_line('No id found');
else
dbms_output.put_line('Most occured is ' || maxName || ', with count: ' || max || '.');

END;
/


And this is the error which a got and can't figure out what is the problem:

1/59 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character
The symbol ":=" was substituted for "(" to continue.

3/71 PLS-00103: Encountered the symbol "JOIN" when expecting one of the following:

, ; for group having intersect minus order start union where
connect


I hope you will understand what I am trying to explain.

Answer

You don't (and can't) specify the size of formal parameters, e.g. max length of string or scale/precision of mumbers. As the documentation says:

Data type of the formal parameter that you are declaring. The data type can be a constrained subtype, but cannot include a constraint (for example, NUMBER(2) or VARCHAR2(20).

So the declaration should just be:

CREATE or REPLACE PROCEDURE myprocedure(XYvalue in VARCHAR2)
is
...

Using count as a column alias isn't a good idea as it's a function name. The cursor query you've posted looks OK though, so if that alias isn't confusing the parser then you may have hidden the issue while changing your table names and other details. Using max as a variable name will also cause problems. Avoid reserved and key words for identifiers and variable names.

Hopefully this is an exercise as you can do what you're trying in plain SQL, without needing to resort to PL/SQL.