How can I make an array from table columns in plsql

Question

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

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;

MT0 · Accepted Answer · 2020-10-08 11:07:58Z

3

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.)

edited Oct 8, 2020 at 11:07

answered Oct 8, 2020 at 10:54

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joe Over a year ago

Sir when i use it like this : SELECT LISTAGG( product_code , ',') WITHIN GROUP ( ORDER BY product_code ) it is working thanks. i think you should update your answer like this.

Collectives™ on Stack Overflow

How can I make an array from table columns in plsql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related