Hi everyone,
I am new to the concept of pseudo functiona and was just trying to use the multiset union function. I have written a cursor which gives me a column 'buyer' and a corresponding dataset. Now my dilemna is how to refer the elements in the dataset using the cursor. Could anyone please help?
CREATE TABLE BUYER
(
BUYER NUMBER(4) NOT NULL,
BUYER_NAME VARCHAR2(120 BYTE) NOT NULL,
BUYER_PHONE VARCHAR2(20 BYTE),
BUYER_FAX VARCHAR2(20 BYTE)
);
SET DEFINE OFF;
Insert into RMS.BUYER
(BUYER, BUYER_NAME, BUYER_PHONE, BUYER_FAX)
Values
(110, 'Kendr', '5147, 876', NULL);
Insert into RMS.BUYER
(BUYER, BUYER_NAME, BUYER_PHONE, BUYER_FAX)
Values
(101, 'Amelie Dube', '(514),808', NULL);
Insert into RMS.BUYER
(BUYER, BUYER_NAME, BUYER_PHONE, BUYER_FAX)
Values
(102, 'Jennifer Baie', '51962,860', NULL);
Insert into RMS.BUYER
(BUYER, BUYER_NAME, BUYER_PHONE, BUYER_FAX)
Values
(103, 'Loriannstris', '5333962,785', NULL);
Insert into RMS.BUYER
(BUYER, BUYER_NAME, BUYER_PHONE, BUYER_FAX)
Values
(104, 'Sandra St-re', '513962,736', NULL);
COMMIT;
--------------------------------------------------------------------------------------------------------------
create or replace type tab1 is table of varchar2 (30);
--------------------------------------------------------------------------------------------------------------
DECLARE
cursor c1 is
select buyer,
CAST (multiset(select buyer_name from buyer where rownum<4) as tab1) as data1
from buyer
MULTISET UNION ALL
select buyer,
CAST(multiset(select buyer_name from buyer where rownum<5) as tab1) as data2
from buyer;
BEGIN
for c_rec in c1
loop
DBMS_OUTPUT.PUT_LINE(c_rec.buyer||'~'||c_rec.column_value(1)); -- i want to print all the values in the 'data set' along with the buyer id.
end loop;
end;
error:
PLS-00302: component 'COLUMN_VALUE' must be declared