How to get column names from a query?

Question

I have a specific query with joins and aliases, and I need to retrieve columns name for a REST request in Talend.

I'm using Talend Open Studio for Data Integration 6.2 and I've got an Oracle 11g database with a read-only account. I can execute scripts with Talend, For example the query:

select 
    u.name as "user", 
    f.name as "food", 
    e.rate 
from 
    Users as u 
    join Eval as e on u.user_id = e.user_id 
    join Food as f on e.food_id = f.food_id
where
    1 = 1

should give the following result:

+------+--------+------+
| user | food   | rate |
+------+--------+------+
| Baba | Donuts | 16.0 |
| Baba | Cheese | 20.0 |
| Keke | Pasta  | 12.5 |
| Keke | Cheese | 15.0 |
+------+--------+------+

And I try to get the columns (in the right order) as follows by using scripts or Talend:

+--------+
| Column |
+--------+
| user   |
| food   |
| rate   |
+--------+

Is there a way to query the Oracle database to get the columns or using talend to retrieve them?

UPDATE

Thanks to Marmite Bomber, a duplicate has been identified here for the Oracle approach. Now we need a Talend approach to the problem.

See the answer to the duplicated question [here](https://stackoverflow.com/a/55345654/4808122) you may use either `DBMS_SQL` or `JDBC` approach — Marmite Bomber, May 02 '19 at 18:04

score 2 · Accepted Answer · answered May 09 '19 at 08:05

You can try this on a tJavaRow, following your DBInput component :

for (java.lang.reflect.Field field: row1.getClass().getDeclaredFields()) {
context.columnName = field.getName();
      System.out.println("Field name is " + context.columnName );      
           }

Spotted on talend help center here : https://community.talend.com/t5/Design-and-Development/resolved-how-to-get-the-column-names-in-a-data-flow/td-p/99172

You can extend this, and put the column list on your outputflow :

//add this inside the loop, and 'columnNames' as an output row in tJavaRow schema

             output_row.columnNames+=context.columnName+";";

With a tNormalize after tJavaRow, you shoud get the expected result.

Thanks ! It works perfectly, I also add a "rownum = 1" in the where clause for performance purpose :) — Ceci Semble Absurde., May 09 '19 at 08:48

score 0 · Answer 2 · answered May 02 '19 at 09:23

0

Here´s a link to an oracle community thread which should answer your question

community.oracle.com

answered May 02 '19 at 09:23

It's usefull if we want columns from a table but in my query I've multiple tables and I don't want all the columns from all the tables, just the selected ones – Ceci Semble Absurde. May 02 '19 at 09:26

score 0 · Answer 3 · answered May 08 '19 at 18:45

0

I am not able to write a comment, so posting this as an answer:

SELECT column_name
  FROM all_tab_cols
 WHERE table_name = 'table_name_here'

answered May 08 '19 at 18:45

yoga

710
5
11

1

Sorry yoga, your answer isn't correct. With your request you can only get the column from a defined table. With your approach to answer the question, you should create a temporary table based on the request and then select the columns names. The problem is I can't write on the database for security requirements. – Ceci Semble Absurde. May 09 '19 at 07:36

How to get column names from a query?

UPDATE

3 Answers3