All,
I am trying to select the column names which start with 'E' in my sql query projection area. For this i have used USER_TAB_COLS with WMSYS.WM_CONCAT / LISTAGG too.
I got the column names, with ',' delimeter, but not able inject this to the sql query. Please help me.
SQL> SELECT WMSYS.WM_CONCAT(column_name)
2 FROM user_tab_cols
3 WHERE table_name='EMP'
4 AND column_name LIKE 'E%';
WMSYS.WM_CONCAT(COLUMN_NAME)
--------------------------------------------------------------------------------
EMPNO,ENAME
SQL> SELECT
2 (SELECT WMSYS.WM_CONCAT(column_name)
3 FROM user_tab_cols
4 WHERE table_name='EMP'
5 AND COLUMN_NAME LIKE 'E%'
6 ) a
7 FROM EMP where rownum<=4;
A
--------------------------------------------------------------------------------
EMPNO,ENAME
ERROR:
ORA-22922: nonexistent LOB value
---LISTAGG
SQL> select (select ListAgg(column_name,',')
2 WITHIN GROUP(ORDER BY COLUMN_NAME DESC) AS CONCATV
3 from user_tab_cols
4 WHERE TABLE_NAME='EMP'
5 AND COLUMN_NAME LIKE 'E%') a FROM EMP WHERE ROWNUM<=3;
A
----------------------------------------------------------------------------------------------------
ENAME,EMPNO
ENAME,EMPNO
ENAME,EMPNO
Thanks,