CODEWITHSUNDEEP
arraysoracle-databaseplsql
joe
joe

Reputation: 79

How can I make an array from table columns in plsql

I have a table and there is a column and I get this column values like this with my code:

100
533
523
634
552
.
.
.

There are many rows like this and then I want make that like one row (100,533,634,552,.,.,.,.) add all of them to v_products or to an array. How can I do this?

Here is my code:

v_products  varchar2(4000);

for c in (select distinct ugb.product_code
          into v_products 
          from uat.product_grup ug,uat.product_grup_con ugb
          where ug.product_grup_code like '%PRODUCT' )
loop
  v_products  := c.product_code;
  dbms_output.put_line(v_products);
end loop;

Upvotes: 0

Views: 3018

Answers (1)

MT0
MT0

Reputation: 167962

If you want them as a string then use LISTAGG.

DECLARE
  v_products  varchar2(4000);
BEGIN
  SELECT LISTAGG( product_code, ',' ) WITHIN GROUP ( ORDER BY product_code )
  INTO   v_products
  FROM   (
    select distinct ugb.product_code
    from   uat.product_grup ug
           CROSS JOIN uat.product_grup_con ugb
    where ug.product_grup_code like '%PRODUCT'
  );
END;
/

If you want them as a collection (or VARRAY) then use BULK COLLECT INTO:

DECLARE
  TYPE product_code_list IS TABLE OF uat.product_grup_con.product_code%TYPE;
  v_products product_code_list;
BEGIN
  select distinct ugb.product_code
  BULK COLLECT INTO v_products
  from   uat.product_grup ug
         CROSS JOIN uat.product_grup_con ugb
  where ug.product_grup_code like '%PRODUCT';
END;
/

(An aside, are you sure you want a legacy comma join without a correlating join condition in the WHERE clause, which is equivalent of am ANSI CROSS JOIN? Without a join condition, all you are doing is getting every product_code value from the uat.product_grup_con table. What you probably want is an INNER JOIN.)

Upvotes: 3

Related Questions