Column Names into a Variable

[klornpallier]

I have a table containing column names of other tables. I need to place them into variables & use them to write to UTL_file in a csv. i.e.

Table Column
------------------
Table A Column A
Table A Column B

Output File:

Column A, Column B

Its probably really easy if you know how but an example would be appreciated.

 

[LKBrwnDBA]

Check out the WS_CONCAT() aggregate function.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[klornpallier]

Is this function part of Oracle 9i as I cant find any reference to it? I have CONCAT but that just gives me Table A and Column A concatinated together not Column A, Column B like I require!

 

[LKBrwnDBA]

Oooops, the name is WM_CONCAT...

WM_CONCAT is a function from workspace manager (WMSYS if you have it installed)

Otherwise you can use the SYS_CONNECT_BY_PATH() function.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Check it out:

SQL> SELECT wm_concat (column_name)
  2    FROM user_tab_columns
  3   WHERE table_name = 'EMPLOYEES'
  4  /

WM_CONCAT(COLUMN_NAME)
--------------------------------------------------------------------------------
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMM
ISSION_PCT,MANAGER_ID,DEPARTMENT_ID

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

klornpallier,

Since you did not respond to LKBrwnDBA's suggestions (including "...you can use the SYS_CONNECT_BY_PATH() function."), I presume that you ran into trouble resolving your need.

In your original post, you said:

I have a table containing column names of other tables.

Such a resource exists for everyone, named "USER_TAB_COLUMNS" (and also, "ALL_TAB_COLUMNS" and "DBA_TAB_COLUMNS"). Here is code that illustrates how to display (as you requested) table names along with a single line that strings together the column names for the table:

select table_name, substr(max(sys_connect_by_path(column_name,',')),2) Columns
  from (select table_name
              ,column_name
              ,row_number() over (partition by table_name order by column_id) rn
          from user_tab_columns
         where table_name in ('S_REGION','S_DEPT','S_EMP')
       )
 start with rn=1
connect by prior rn = rn - 1
       and prior table_name = table_name
 group by table_name
 order by table_name
/

TABLE_NAME                     COLUMNS
------------------------------ -------------------------------------------------------------------------------------------------
S_DEPT                         ID,NAME,REGION_ID
S_EMP                          ID,LAST_NAME,FIRST_NAME,USERID,START_DATE,COMMENTS,MANAGER_ID,TITLE,DEPT_ID,SALARY,COMMISSION_PCT
S_REGION                       ID,NAME

Let us know if this gives you the model that you need to resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”