Home » SQL & PL/SQL » SQL & PL/SQL » Problem with comparing collections
Problem with comparing collections [message #574561] Sat, 12 January 2013 18:58 Go to next message
annaBaum
Messages: 1
Registered: January 2013
Location: Germany
Junior Member
Hi to everyone! Could someone help me with the following code..My big problem ist to compare collection in if statements. I was trying to do this with MEMBER OF.. I can not reach the column Nut_NutzerId in collection: v_freundeVonFreunden. Wanted to do: v_freundeVonFreunden.Nut_NutzerId but it s not possible.. in this place im getting errors:

FOR v_i IN 1..v_sichtbarImAlbum.COUNT LOOP
-- i need to check if NutzerId from v_sichtbarImAlbum(v_i) is same with Nut_NutzerId from v_freundeVonFreunden----
IF v_sichtbarImAlbum(v_i) MEMBER OF v_freundeVonFreunden THEN -- ***error
v_empfaenger.EXTEND;
v_empfaenger(v_empfaenger.COUNT):=v_sichtbarImAlbum(v_i);
v_countFreundeVonFreunden := v_countFreundeVonFreunden +1;
END IF;

IF v_sichtbarImAlbum(v_i) MEMBER OF v_freundeList AND v_sichtbarImAlbum(v_i) NOT MEMBER OF v_empfaenger THEN ---***error
v_empfaenger.EXTEND;
v_empfaenger(v_empfaenger.COUNT):=v_sichtbarImAlbum(v_i);
END IF;


The complete code is under.
Could someone give me a clue, how can i do it? I would extremely appreciate it. I m trying whole day without success. I know code is in German, so if required , i can translate what is unclear. I m also attaching the file with tables.


Im getting following errors in sql developer:

Package Body U539010.NACHRICHT_SERVICE@u539010@oraceins
Fehler(91,7): PL/SQL: Statement ignored
Fehler(91,10): PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'MEMBER OF' = wrong amount or type of argument in call of 'MEMBER OF'
Fehler(97,7): PL/SQL: Statement ignored
Fehler(97,10): PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von 'MEMBER OF'

hier start of code:
-----------------------------------------------------------------------------------------------------------------------------
create or replace
PACKAGE BODY nachricht_service AS

cursor cur_findeFreunde(v_userId INTEGER)
return befreundet%rowtype
IS
SELECT be.Nut_NutzerId, be.NutzerId, be.bestatigt
FROM Befreundet be, Nutzer nu
WHERE nu.NutzerId = be.NutzerId AND
be.bestatigt =1 AND
nu.NutzerId = v_userId;

PROCEDURE erstelle_empfaenger(p_nachrichtid Nachricht.NachrichtId%TYPE,
p_albumid Medium.MedienId%TYPE) AS

TYPE t_nutzerIdList IS TABLE OF zeigt.NutzerId%Type;
v_sichtbarImAlbum t_nutzerIdList;
v_empfaenger t_nutzerIdList;

v_absenderId nutzer.NutzerId%TYPE;
v_sichtbarkeitsTyp SichtbarkeitsTyp.Bezeichnung%TYPE;

--TYPE t_freundeList IS TABLE OF befreundet%ROWTYPE;
type r_freundeList is record (
Nut_NutzerId befreundet.Nut_NutzerId%TYPE,
NutzerId befreundet.NutzerId%TYPE,
bestatigt befreundet.bestatigt%TYPE
);
TYPE t_freundeList IS TABLE OF r_freundeList;

v_freundeList t_freundeList;
v_freundeVonFreunden t_freundeList;

v_countFreundeVonFreunden INTEGER:=0;

BEGIN

--Absender bestimmen
SELECT nu.NutzerId INTO v_absenderId
FROM Nachricht na, Nutzer nu
WHERE na.NutzerId = nu.NutzerId AND
na.NachrichtId = p_nachrichtid;


--Sichtbarkeitstyp des Albums bestimmen
SELECT si.Bezeichnung INTO v_sichtbarkeitsTyp
FROM SichtbarkeitsTyp si, Medium me, Pinnwandeintrag pi
WHERE me.MedienId = pi.MedienId AND
pi.STId = si.STId AND
me.MedienId = p_albumId;

--Exception werfen, falls das Album nicht für Freunde von Freunden freigegeben wurde

IF v_sichtbarkeitsTyp <> 'Freunde von Freunden' THEN
RAISE hidden_medium;
END IF;


--Nutzer bestimmen, welche im Album gezeigt werden

SELECT ze.NutzerId BULK COLLECT INTO v_sichtbarImAlbum
FROM Zeigt ze, Medium me, MedienArt ma
WHERE ze.MedienId = me.MedienId AND
me.MArtId = ma.MArtId AND
ma.Bezeichnung = 'Fotoalbum' AND
me.MedienId = p_albumid;


--Freunde des Nachrichtenautors bestimmen
open cur_findeFreunde(v_absenderId);
while cur_findeFreunde%found loop
v_freundeList.EXTEND;
fetch cur_findeFreunde into v_freundeList(v_freundeList.COUNT);
end loop;
close cur_findeFreunde;

--Freunde von Freunden des Nachrichtenautors bestimmen

FOR v_i IN 1..v_freundeList.COUNT LOOP
open cur_findeFreunde(v_freundeList(v_i).Nut_NutzerId);
while cur_findeFreunde%found loop
v_freundeVonFreunden.EXTEND;
fetch cur_findeFreunde into v_freundeVonFreunden(v_freundeList.COUNT);
end loop;
close cur_findeFreunde;
END LOOP;

----------------- in this part im getting errors !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!-----------------
-- i need to check if NutzerId from v_sichtbarImAlbum(v_i) is equal with Nut_NutzerId from v_freundeVonFreunden----

--Freunde und Freunde von Freunden, welche im Album sichtbar sind zu der Emfaengerliste hinzufuegen

FOR v_i IN 1..v_sichtbarImAlbum.COUNT LOOP

IF v_sichtbarImAlbum(v_i) MEMBER OF v_freundeVonFreunden THEN
v_empfaenger.EXTEND;
v_empfaenger(v_empfaenger.COUNT):=v_sichtbarImAlbum(v_i);
v_countFreundeVonFreunden := v_countFreundeVonFreunden +1;
END IF;

IF v_sichtbarImAlbum(v_i) MEMBER OF v_freundeList AND v_sichtbarImAlbum(v_i) NOT MEMBER OF v_empfaenger THEN
v_empfaenger.EXTEND;
v_empfaenger(v_empfaenger.COUNT):=v_sichtbarImAlbum(v_i);
END IF;

END LOOP;

--Exception werfen, fall keine Freunde von Freunden gefunden werden

IF v_countFreundeVonFreunden =0 THEN
RAISE no_receiver;
END IF;


--Empfaenger in Datenbank schreiben (an)

FOR v_i IN 1..v_empfaenger.COUNT LOOP
INSERT INTO an VALUES (p_nachrichtid, v_empfaenger(v_i));
END LOOP;


END erstelle_empfaenger;


END nachricht_service;
  • Attachment: rel.jpg
    (Size: 168.47KB, Downloaded 69 times)
Re: Problem with comparing collections [message #574567 is a reply to message #574561] Sun, 13 January 2013 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Note that we do NOT need your actual code.
Just build a SIMPLE example that shows your problem and explain it.

Regards
Michel
Re: Problem with comparing collections [message #574578 is a reply to message #574561] Sun, 13 January 2013 08:40 Go to previous message
Solomon Yakobson
Messages: 1960
Registered: January 2010
Senior Member
MEMBER OF implies comparison. Oracle compares nested tables elements one-by-one with provided expression. In your case nested table elements are of PL/SQL RECORD type. Since PL/SQL doesn't support RECORD comparison, MEMBER OF fails. To compare RECORDs there is no other way but compare corresponding attributes. If you'd use SQL OBJECT type instead of PL/SQL RECORD type you could define SQL OBJECT type MAP method and then use not just MEMBER OF but simply compare nested tables:

SQL> create or replace
  2    type xudt as object(
  3                        x number,
  4                        a varchar2(10),
  5                        map member function xkey return varchar2
  6                       );
  7  /

Type created.

SQL> create or replace
  2    type body xudt
  3      as
  4        map member function xkey
  5          return varchar2
  6          is
  7          begin
  8              return CHR(1) || x || CHR(1) || a || CHR(1);
  9          end;
 10  end;
 11  /

Type body created.

SQL> create or replace
  2    type xudt_tab
  3      as table of xudt;
  4  /

Type created.

SQL> begin
  2      if xudt_tab(xudt(1,'A'),xudt(2,'B'),xudt(3,'C')) = xudt_tab(xudt(1,'A'),xudt(2,'B'),xudt(3,'C'))
  3        then dbms_output.put_line('Equal');
  4        else dbms_output.put_line('Not equal');
  5      end if;
  6  end;
  7  /
Equal

PL/SQL procedure successfully completed.

SQL> begin
  2      if xudt_tab(xudt(1,'A'),xudt(2,'B'),xudt(3,'C')) = xudt_tab(xudt(1,'A'),xudt(2,'D'),xudt(3,'C'))
  3        then dbms_output.put_line('Equal');
  4        else dbms_output.put_line('Not equal');
  5      end if;
  6  end;
  7  /
Not equal

PL/SQL procedure successfully completed.

SQL>


However, keep in mind nested table type models an unordered set of elements. Therefore comparison operation will consider two nested tables with same set of elements equal even if elements in nested tables are in different order:

SQL> begin
  2      if xudt_tab(xudt(1,'A'),xudt(3,'C'),xudt(2,'B')) = xudt_tab(xudt(1,'A'),xudt(2,'B'),xudt(3,'C'))
  3        then dbms_output.put_line('Equal');
  4        else dbms_output.put_line('Not equal');
  5      end if;
  6  end;
  7  /
Equal

PL/SQL procedure successfully completed.

SQL>


SY.
P.S. MAP method used in my example is suitable for equality operator only. It might produce wrong resuls for greater/less and other comparison operations.

[Updated on: Sun, 13 January 2013 08:44]

Report message to a moderator

Previous Topic: How to find tables which does not have specific columns
Next Topic: Your kindly help in this query (pivot)
Goto Forum:
  


Current Time: Mon Jul 28 12:25:32 CDT 2014

Total time taken to generate the page: 0.09395 seconds