I need to print out male and female friends in separate lists using loop, however receive the error:
' loop 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:'
DECLARE
v_gender VARCHAR2(500);
v_male VARCHAR2(1);
v_female VARCHAR2(1);
BEGIN
FOR i IN (select * from friend_name)
LOOP
v_gender := 'Gender ' || i.gender ||', '|| i.first_name ||', '|| i.last_name;
dbms_output.put_line(v_gender);
v_male := null;
v_female := null;
FOR J IN (SELECT * FROM friend_name)LOOP
IF(j.gender = 'M')
THEN dbms_output.put_line(first_name ||', '|| last_name ||', '|| v_male);
ELSE
dbms_output.put_line(first_name ||', '|| last_name ||', '|| v_female);
END IF;
END LOOP;
END;
Could you help me figure out what's missing?
Error you got is related to the fact that you didn't END the first LOOP. If you fix it, you'll get another error as you didn't properly qualify the J cursor variables (i.e. not just first_name, but j.first_name).
When fixed, with sample table:
SQL> SELECT * FROM friend_name;
GENDER FIRST_NAME LAST_NAME
------- --------------- ---------------
M Little Foot
M Scott Tiger
F Big Feet
procedure works
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_gender VARCHAR2 (500);
3 v_male VARCHAR2 (1);
4 v_female VARCHAR2 (1);
5 BEGIN
6 FOR i IN (SELECT * FROM friend_name)
7 LOOP
8 v_gender := 'Gender ' || i.gender || ', ' || i.first_name || ', ' || i.last_name;
9 DBMS_OUTPUT.put_line (v_gender);
10
11 v_male := NULL;
12 v_female := NULL;
13 END LOOP;
14
15 FOR J IN (SELECT * FROM friend_name)
16 LOOP
17 IF (j.gender = 'M')
18 THEN
19 DBMS_OUTPUT.put_line (j.first_name || ', ' || j.last_name || ', ' || v_male);
20 ELSE
21 DBMS_OUTPUT.put_line (j.first_name || ', ' || j.last_name || ', ' || v_female);
22 END IF;
23 END LOOP;
24 END;
25 /
and produces this output:
Gender M, Little, Foot
Gender M, Scott, Tiger
Gender F, Big, Feet
Little, Foot,
Scott, Tiger,
Big, Feet,
PL/SQL procedure successfully completed.
SQL>
I have no idea whether that's what you really wanted as you didn't post desired result, but - consider something like this:
SQL> DECLARE
2 v_male VARCHAR2 (10000);
3 v_female VARCHAR2 (10000);
4 BEGIN
5 FOR i IN (SELECT gender, first_name, last_name FROM friend_name)
6 LOOP
7 IF i.gender = 'M'
8 THEN
9 v_male := v_male || ', ' || i.first_name || ' ' || i.last_name;
10 ELSIF i.gender = 'F'
11 THEN
12 v_female := v_female || ', ' || i.first_name || ' ' || i.last_name;
13 END IF;
14 END LOOP;
15
16 v_male := LTRIM (v_male, ', ');
17 v_female := LTRIM (v_female, ', ');
18
19 DBMS_OUTPUT.put_line ('Male: ' || v_male);
20 DBMS_OUTPUT.put_line ('Female: ' || v_female);
21 END;
22 /
Male: Little Foot, Scott Tiger
Female: Big Feet
PL/SQL procedure successfully completed.
SQL>
Or, maybe using collections?
SQL> DECLARE
2 v_male SYS.odcivarchar2list;
3 v_female SYS.odcivarchar2list;
4 BEGIN
5 SELECT first_name || ' ' || last_name
6 BULK COLLECT INTO v_male
7 FROM friend_name
8 WHERE gender = 'M';
9
10 SELECT first_name || ' ' || last_name
11 BULK COLLECT INTO v_female
12 FROM friend_name
13 WHERE gender = 'F';
14
15 DBMS_OUTPUT.put_line ('Male:');
16
17 FOR i IN v_male.FIRST .. v_male.LAST
18 LOOP
19 DBMS_OUTPUT.put_line (v_male (i));
20 END LOOP;
21
22 DBMS_OUTPUT.put_line ('Female:');
23
24 FOR i IN v_female.FIRST .. v_female.LAST
25 LOOP
26 DBMS_OUTPUT.put_line (v_female (i));
27 END LOOP;
28 END;
29 /
Male:
Little Foot
Scott Tiger
Female:
Big Feet
PL/SQL procedure successfully completed.
SQL>
There are various options (as usual).