CODEWITHSUNDEEP
oracle-databaseplsql
James Zhang
James Zhang

Reputation: 29

Why my output is blank when I run the script

I want to run this code on PL/SOL to show how many activities in sporting clubs are on my database. Here is my code:

declare
Sporting_clubs_name sporting_clubs.name%type;
Count_of_activity number(2);
begin
for Sporting_clubs_name in
    (select sporting_clubs.name,
     count(club_activity.activity)
     into Sporting_clubs_name, Count_of_activity
     from sporting_clubs, club_activity
     where sporting_clubs.club_id = club_activity.club_id
     group by sporting_clubs.name)
loop
dbms_output.put_line('The '||Sporting_clubs_name||' has '||Count_of_activity||' activities.');
end loop;
end;

When I run it shows:

The  has  activities.
The  has  activities.
The  has  activities.
The  has  activities.

It should be shown the sporting club's name and count of activity of the output, but the "Sporting_clubs_name" and "Count_of_activity" is blank.

I also change "Sporting_clubs_name" to varchar2(20), but it still not working.

What is wrong is my code, and how can I fix it?

Upvotes: 1

Views: 68

Answers (2)

Ali Fidanli
Ali Fidanli

Reputation: 1372

just use implicit cursor loop template like here

begin
for c in (select sporting_clubs.name,
     count(club_activity.activity) as cnt    
     from sporting_clubs, club_activity
     where sporting_clubs.club_id = club_activity.club_id
     group by sporting_clubs.name )
loop
  dbms_output.put_line('The '||c.name||' has '||c.cnt||' activities.');

end loop;
end;

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40499

You cannot use select ... into in a PL/SQL for .. in (select..) loop.

Sporting_clubs_name is the name of the loop-varable and a variable whowse type corresponds to sporting_clubs.name%type.

I'd think that the code you posted doesn't even compile.

Remove your variables (Sporting_clubs_name and Count_of_activity), give count(club_activity.activity) an alias (for example cnt) and then use dbms_output.put_line('The '||Sporting_clubs_name.name||' has '||Sporting_clubs_name.cnt||' activities.'); instead.

Upvotes: 4

Related Questions