How can I “group” several rows into a collection in my PL / SQL course?

I have a pointer where I need to get a whole bunch of information for the interface, but one of the tables that I need to get the information has one row for each information I need in the columns.

I looked into PIVOT, and at first it seemed to me that it would be a mess (especially considering that I do not need an aggregate for this), but I managed to get it to work well; but I am a consistent student, so I still want to know if this is possible:

I thought about this, selecting the table as a column: "type table of (object)", so I will have an array of array in my PL / SQL code ... and it worked fine! When the property table had only one row, but I got ORA-01427 when it had several. Here is a short sample code (for simplicity, I only use tables of identifiers and addresses, this is the actual small PL that I created to test this functionality):

CREATE OR REPLACE TYPE CAIB_FIELDS AS OBJECT (
ID_QUALIFIER  VARCHAR2(3),
ID_NUMBER     VARCHAR2(20)
)
/
CREATE TYPE CAIB_TBL AS TABLE OF CAIB_FIELDS
/

DECLARE
CURSOR MYCUR(CID IN VARCHAR2) IS
SELECT CUST_ID,CUST_ADDR,UPD_DATE
(SELECT CAIB_TBL(CAIB_FIELDS(ID_QUALIFIER,ID_NUMBER)) FROM CUSTOMER_IDS B
  WHERE B.CUST_ID = A.CUST_ID
  AND   B.CUST_ADDR = A.CUST_ADDR) CAIB
FROM CUSTOMER_ADDR A
WHERE A.CUST_ID = CID
;
   TYPE MYCUR_TYPE IS TABLE OF MYCUR%ROWTYPE;
    REC_MYCUR MYCUR_TYPE;
BEGIN
    OPEN MYCUR('918888'); --This customer has only one ID row -> OK! IT WORKS!
    --For customer ID '002632', he has several ID ROWS -> ERROR ORA-01427
    LOOP
        FETCH MYCUR BULK COLLECT INTO REC_MYCUR LIMIT 100;
        FOR I IN 1..REC_MYCUR.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).CAIB(1).ID_QUALIFIER);
            --DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).CAIB(2).ID_QUALIFIER); --this would be OK if Oracle would allow me to fetch multiple rows on my CAIB_TBL type, of course I'll just loop here if it works, but for the sake of this test, I just used fixed values..
        END LOOP;
        EXIT WHEN MYCUR%NOTFOUND;
    END LOOP;
END;

Thanks in advance!

--- EDIT, tbone's answer is exactly what I was looking for, but it does not reflect the exact scenario, since it concerns only one table of columns; for several columns the solution changes slightly, here is my last test:

create table testA
(
col1 number,
col2 varchar2(50)
);

create table testB
(
col1 number,
col2 varchar2(50),
col3 varchar2(50)
);

insert into testA values (1,'A');
insert into testA values (2,'B');
insert into testA values (3,'C');

insert into testB values (1,'X','x');
insert into testB values (1,'Y','y');
insert into testB values (1,'Z','z');

insert into testB values (2,'BA','ba');
insert into testB values (2,'BB','bb');
commit;


CREATE OR REPLACE TYPE t_test_rec AS object
(col2 varchar2(50),
col3 varchar2(50)
)
/
create or replace type t_vchar_tab as table of t_test_rec;

DECLARE
CURSOR MYCUR IS
SELECT A.COL1, 
CAST(MULTISET(SELECT B.COL2,B.COL3 FROM TESTB B WHERE B.COL1 = A.COL1 ORDER BY B.COL2) AS T_VCHAR_TAB) AS TESTB_VALS
FROM TESTA A
;
   TYPE MYCUR_TYPE IS TABLE OF MYCUR%ROWTYPE;
    REC_MYCUR MYCUR_TYPE;
BEGIN
    OPEN MYCUR;
    LOOP
        FETCH MYCUR BULK COLLECT INTO REC_MYCUR LIMIT 100;
        FOR I IN 1..REC_MYCUR.COUNT
        LOOP
            IF REC_MYCUR(I).TESTB_VALS.COUNT = 0 THEN
                DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).COL1 || '->(NULL)');
            ELSE
                FOR J IN 1..REC_MYCUR(I).TESTB_VALS.COUNT
                LOOP
                   DBMS_OUTPUT.PUT_LINE(REC_MYCUR(I).COL1 || '->' || REC_MYCUR(I).TESTB_VALS(J).COL2 || ',' || REC_MYCUR(I).TESTB_VALS(J).COL3);
                   NULL;
                END LOOP;
            END IF;
        END LOOP;
        EXIT WHEN MYCUR%NOTFOUND;
    END LOOP;
END;
/
+3
source share
1 answer

, , ( ) - :

set echo on;
set display on;
set linesize 200;

create table testA
(
col1 number,
col2 varchar2(50)
);

create table testB
(
col1 number,
col2 varchar2(50)
);

create or replace type t_vchar_tab as table of varchar2(50);

insert into testA values (1,'A');
insert into testA values (2,'B');

insert into testB values (1,'X');
insert into testB values (1,'Y');
insert into testB values (1,'Z');
commit;

-- select all related testB.col2 values in a nested table for each testA.col1 value
select a.col1, 
cast(multiset(select b.col2 from testB b where b.col1 = a.col1 order by b.col2) as t_vchar_tab) as testB_vals
from testA a;

, 2 A, , tableB

+4

All Articles