Oracle rename columns from select automatically?

bwawok picture bwawok · Sep 30, 2010 · Viewed 15.7k times · Source

I have 2 tables with the following fields.

Table1

  • AA
  • BB
  • CC
  • DD

Table2

  • AA
  • CC
  • EE

Query

Select t1.*, 
       t2.*
  from table1 t1,
  join table2 t2 on table1.DD = table2.EE

My data columns back with the following column names:

AA, BB, CC, DD, **AA_1**, **CC_1**, EE

I don't want the column names like that. I want them to have the table name prefixed in the names of common (or all columns). I could fix this with:

select t1.AA as t1_AA, t1.BB as t1_BB, t1.CC as t1_CC, t1.DD as t1_DD, 
 t2.AA as t2_AA, t2.CC as t2_CC, t2.EE as t2_EEE
   from table1 t1,
    inner join table2 t2
    on table1.DD = table2.EE

But that means every select everywhere becomes 500 lines longer. Is there a magic way to do this in oracle? Basically I want to write my code like

 select t1.* as t1_*, t2.* as t2_*
       from table1 t1,
        inner join table2 t2
        on table1.DD = table2.EE

But of course that is not valid SQL

Answer

OMG Ponies picture OMG Ponies · Sep 30, 2010

Is there a magic way to do this in oracle?

Not that I'm aware of. Your options amount to:

  1. Address the column naming scheme - you'd need to use ALTER TABLE statements like:

    ALTER TABLE table_name
         RENAME COLUMN old_name to new_name;
    
  2. Use column aliases

You could use views to save on the work & effort of defining column aliases, but it's not a recommended practice because of the bad performance when layering views on top of one another.