Oracle rename columns from select automatically?

Question

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 1

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.

Answer 2

In Oracle SELECT syntax, there is currently no way to assign column aliases to multiple columns based on some expression. You have to assign an alias to each individual column.

Answer 3

Is creating a view an option?

What is the software you're using that does this to you? I don't see this behavior in SQL*Plus or PL/SQL Developer in 10g. PL/SQL won't let you build a cursor with this ambiguity in it.