alias all column in a query with a prefix

Question

it's possible to alias all column with a prefix, in a select? I have a group of tables in an Oracle db that I need to join together, and most of them have the same column names. I would like to have something like

select MAGIC_ADD_PREFIX("PREFIX", *) from TABLE

and have a result like

+---------+----------+----------+
|PREFIX_ID|PREFIX_FOO|PREFIX_BAR|
+---------+----------+----------+
|...      |          |          |

ATM the only thing I can think is something chumsky like

select ID PREFIX_ID, FOO PREFIX_FOO, BAR PREFIX_BAR from TABLE

but it's ugly as hell and error-prone

==== further explanation ====

The problem with

select TABLE.*,...

is that I'm using java + jdbc drivers to retrieve the columns, and the java.sql.ResultSet methods (resultset.getInt("COLUMNNAME"), .getString("COLUMNNAME")...) doesn't support the syntax "TABLENAME.COLUMNAME".

if I do (simplified, no error cheks...)

ResultSet rs = mkResultSet("select * from table_a, table_b");
rs.next();
System.out.println(rs.getInt("table_a.id"));

I get a SQLException with invalid column name as message

Answer 1

Depends on what you use to execute your query. Even in SQL*Plus you could use variables. Here's an example. In PL/SQL it's even easier. Could you specify where you'll use the query?

Answer 2

You can do

select a.*, b.* from table_a a, table_b b where.....

Or you could create views over the base tables like

   create view a_vw as select a.col1 a_col1, a.col2 a_col2...

You could generate the SQL for creating a_vw fairly easily from selecting the column name from user_tab_columns