2
select wm_concat(COLUMN_NAME) A FROM ALL_TAB_COLUMNS where table_name like 'T_EMPLOYEE';

How to extract the data out from A and assign it to another varchar2 variable?

kmy
  • 55
  • 6
  • Since wmsys.wm_concat will give you a comma separated list, i am assuming you need to separate out the values in the list? – Avrajit Roy Dec 15 '15 at 09:47

2 Answers2

1

If you just need to store it in a VARCHAR2 variable for further processing, you can do it with something like:

DECLARE
  BUFF_V VARCHAR2(2000); -- make sure there's enough space or add some substr
BEGIN
  SELECT wm_concat(COLUMN_NAME) 
    INTO BUFF_V 
    FROM ALL_TAB_COLUMNS 
   WHERE TABLE_NAME LIKE 'T_EMPLOYEE'; 
END;

If you don't want to bother with the parsing of the wm_concat, you can just use something like:

DECLARE
  COL_V VARCHAR2(100);
BEGIN
  FOR QUERY_C IN (select COLUMN_NAME FROM ALL_TAB_COLUMNS where table_name like 'T_EMPLOYEE') LOOP
    COL_V := QUERY_C.COLUMN_NAME;
    -- do whatever you want with COL_V
  END LOOP;
END;

This will iterate on all results (all column names of table T_EMPLOYEE) storing them in the COL_V variable.

BigMike
  • 6,683
  • 1
  • 23
  • 24
0

I know there is an accepted answer already but just FYI, wm_concat will not work if your version is upgraded on 12c.

You may use LISTAGG as an alternative so why bother use an undocumented feature.Read this

Just saying.

Cheers =)

brenners1302
  • 1,440
  • 10
  • 18