Return Oracle column names in table.column format?

Question

Is there any parameter or method that I can use to force Oracle to return results in the format <table>.<column> ? For instance:

Query:

 SELECT * FROM foo f INNER JOIN bar b ON b.foo_id = f.id 

Desired Results:

 F.ID F.BLAH B.ID B.FOO_ID B.BLAH -------------------------------------------------------- 1 blah 7 1 blah 2 blah 8 2 blah 3 blah 9 2 blah 

The obvious solution is the individual alias of each column. SELECT f.id AS F_ID, ... ; however, I need to export very large obsolete tables (over 300 columns), so using this method will cause huge and impractical queries.

Answer 1

There is no “option” in Oracle to do this; You can find a client who will allow you to do this, since this is a task that is usually performed in the client; I do not know one thing.

To expand tbone answer , you have to do it dynamically. This does not mean that you need to list each column. You would use a data dictionary , in particular all_tab_columns or user_tab_columns , to create a query. It would be easier to create a view with the exact definition that you want, so that you can reuse it if you want.

The goal is to take advantage of the fact that the existence of columns is stored in a table as a row to create a query to use that column. Because column names and table names are stored as strings, you can use row aggregation methods to easily create a query or DDL statement that you can execute manually or dynamically.

If you are using Oracle 11g Release 2, the listagg function is available to help you:

 select 'create or replace view my_view as select ' || listagg( table_name || '.' || column_name || ' as ' || substr(table_name,1,1) || '_' || column_name, ', ') within group ( order by case when table_name = 'FOO' then 0 else 1 end , column_id ) || ' from foo f join bar b on f.id = b.foo_id' from user_tab_columns where table_name in ('FOO','BAR') ; 

Assuming this table structure:

 create table foo ( id number, a number, b number, c number); create table bar ( foo_id number, a number, b number, c number); 

This single request creates the following:

 create or replace view my_view as select FOO.ID as F_ID, FOO.A as F_A, FOO.B as F_B, FOO.C as F_C , BAR.FOO_ID as B_FOO_ID, BAR.A as B_A, BAR.B as B_B, BAR.C as B_C from foo f join bar b on f.id = b.foo_id 

and here is the SQL Fiddle to prove it.

In that you are not using 11.2, you can achieve exactly the same results using the undocumented function wm_concat or the custom function stragg that was created by Tom Keith. Oracle Base has an article on row aggregation techniques , and there are many reports of stack overflows.

As a small addition, you can actually create what you are looking for with a slight modification to the above query. You can use the specified identifier to create a column in the format TABLE_NAME.COLUMN_NAME . You should quote it since . is not a valid character for an object name in Oracle. The advantage of this is that you get exactly what you want. The downside is that querying the created view is a huge pain if you don't use select * from ... ; selecting named columns will require quoting them.

 select 'create or replace view my_view as select ' || listagg( table_name || '.' || column_name || ' as ' || '"' || table_name || '.' || column_name || '"', ', ') within group ( order by case when table_name = 'FOO' then 0 else 1 end , column_id ) || ' from foo f join bar b on f.id = b.foo_id' from user_tab_columns where table_name in ('FOO','BAR') ; 

This query returns :

 create or replace view my_view as select FOO.ID as "FOO.ID", FOO.A as "FOO.A", FOO.B as "FOO.B", FOO.C as "FOO.C" , BAR.FOO_ID as "BAR.FOO_ID", BAR.A as "BAR.A" , BAR.B as "BAR.B", BAR.C as "BAR.C" from foo f join bar b on f.id = b.foo_id 

Answer 2

Using aliases will not make queries impractical, but simply not as convenient as typing *. Use dynamic SQL to generate columns for you:

 select 'f.' || column_name || ' as F_' || column_name || ',' from all_tab_columns where table_name = 'FOO' order by column_id; 

Do the same for any other tables you need and copy / paste into your query. Also note the 30 char limit, we hope that none of your columns will be larger than 28.