Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Refer Multiset Union value from cursor

KunalAug 14 2012 — edited Aug 15 2012
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
This post has been answered by unknown-7404 on Aug 14 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 12 2012
Added on Aug 14 2012
8 comments
627 views