Skip to Main Content

Usage of MEMBER OF with record type variables

Miquel DespuigNov 23 2022 — edited Nov 23 2022

Hi Community!
I'm trying to use an operator MEMBER OF with record type variables. When I do this, I get a type error. I've managed to write a function that iterates over the records of the nested table variable comparing the items one by one. I wonder if there's a way to make this work either by changing the operation type or changing my data structure.
Here sample code to test:
CREATE TABLE F4108 (
iolotn NCHAR(30),
ioitm NUMBER,
iomcu NCHAR(12),
ioohdj NUMBER(6,0)
);

INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323 ','6880',' 10VILPA1','122228');
INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323 ','6350',' 10VILPA1','122228');
INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323 ','6343',' 10VILPA1','122228');
INSERT INTO F4108 (IOLOTN,IOITM,IOMCU,IOOHDJ) values ('00003323 ','6377',' 10VILPA1','122228');

DECLARE
TYPE t_lots_ntt IS TABLE OF F4108%ROWTYPE;

queue t_lots_ntt := t_lots_ntt();
visited t_lots_ntt := t_lots_ntt();

l_lot F4108%ROWTYPE;
idx PLS_INTEGER;

BEGIN

SELECT * BULK COLLECT INTO queue
FROM F4108;

l_lot := queue(queue.FIRST);

dbms_output.put_line('Record we know is member of the nested table:');
dbms_output.put_line(l_lot.iolotn||' '||l_lot.ioitm||' '||l_lot.iomcu);

dbms_output.put_line('Nested table records: ');
idx := queue.FIRST;
LOOP
EXIT WHEN idx IS NULL;
IF l_lot MEMBER OF queue THEN
dbms_output.put_line(queue(idx).iolotn||' '||queue(idx).ioitm||' '||queue(idx).iomcu);
END IF;
idx := queue.NEXT(idx);
END LOOP;

END;

Comments
Post Details
Added on Nov 23 2022
10 comments
165 views