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');
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);
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;
/