id_client | XY
01 | str1
02 | str2
03 | str1
id_client | id_something
02 | 32
02 | 48
01 | 32
id_something | name
48 | john
32 | george
CREATE or REPLACE PROCEDURE myprocedure(XYvalue in VARCHAR2(100))
cursor countsCursor is select id_something, count(*) count
from table1 join table2 using (id_client)
group by id_something;
max := 0;
fetch countsCursor into cnt;
exit when countsCursor%NOTFOUND;
IF (cnt.count > max) THEN
max := cnt.count;
idMax := cnt.id_something;
select name into maxName from table3 where id_something = idMax;
if (max = 0) THEN
dbms_output.put_line('No id found');
dbms_output.put_line('Most occured is ' || maxName || ', with count: ' || max || '.');
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
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 ...
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.