ORA-00957 View of JOINed tables with same column names

Question

Is there a way to create a view of two joined tables so that

• all columns of both tables are in the view
• and there is a column of the same name in both joined tables
• and the columns are not explicitly enumerated in the view definition, form of * notation is used (maybe except for the columns with conflicting names)?

Following definition

create table t1 (
id number primary key,
name1 varchar(12) not null
);


create table t2 (
id number primary key,
name2 varchar(12) not null
);

create view v1
as select t1.*, t2.*
from t1, t2;

fails with

create view v1
as select t1.*, t2.*
from t1, t2
Error report -
ORA-00957: duplicate column name
00957. 00000 -  "duplicate column name"
*Cause:
*Action:

Database: Oracle 19c

Answer 1

Even if you create a view with an implicit query like select * from t1, that doesn't mean that the view's columns are dynamic. When creating the view, Oracle uses t1.* and t2.* to get explicit lists of column names for the tables at that moment in time from the data dictionary, and creates those columns explicitly in the view. If the source table is altered to add a column later, that column will not suddenly appear in the view: you will need to recreate the view to add the column.

Use of implicit queries in DDL and in code generally is considered unwise: you don't want a change to a table to ripple in unpredictable ways that could break code that depends on explicit conditions. Strong code is explicit code (everything named, fully qualified, etc.).

That said, your command failed specifically because it couldn't reconcile duplicate column names for id between the tables. Also note that without an explicit join, you are creating a cartesian product of the two tables for your view.

If you want a view that is just a union of the two tables (all the data, each column represented once, no joins: id, name), then use a query like this:

create view v1 as
    select t1.id, t1.name1 as name from t1
    union
    select t2.id, t2.name2 as name from t2

If you want a view that shows all the columns for both tables in a single row (i.e. t1.c1, t2.c1, t1.c2, t2.c2) then use a query like this, but join the rows in the two tables on the primary key:

create view v1 as
    select t1.id as id1, 
           t2.id as id2, 
           t1.name1 as name1, 
           t2.name2 as name2
      from t1
      join t2 on (t2.id = t1.id)

Either way, use explicit naming in your queries so that your code will always match what's in the data dictionary and you won't accidentally break something 8 levels of dependency away.