You Asked

Hi Tom,

During a recent interview, I was asked that what happens to a view, if the base table is modified. For eg.
I have a table emp with 3 columns viz eid, did, sal. I have created a view vw_emp as CREATE VIEW vw_emp AS SELECT * FROM emp; Things are working fine now. A biz req came in which says a column to be added in the table emp, so my base table emp now has 4 columns viz eid, did, sal, loc.. What will be the result of view now, will it be having 3 columns or 4 columns. Please explain.
Thanks in advance

and Connor said...

We dont store the "*", we resolve out the column names, eg

SQL> create table t ( a int, b int, c int );

Table created.

SQL> create or replace view V
  2  as select * from T;

View created.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
-----------------------------------------------------
select "A","B","C" from T

So when you add a column, the view will not see it.

SQL> alter table T add d int;

Table altered.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
-----------------------------------------------
select "A","B","C" from T

1 row selected.

Hope this helps.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Govinda Gujrati, October 22, 2016 - 7:46 am UTC

Thanks a ton for the response. I was wondering that what would happen if a column is removed from the table. For eg.. we have dropped a column from table and now table has only 2 columns.

October 24, 2016 - 12:38 am UTC

SQL> create table t ( a int, b int, c int );

Table created.

SQL>
SQL> create or replace view V
  2  as select * from T;

View created.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
--------------------------------------------------------
select "A","B","C" from T

SQL>
SQL> alter table t drop column c;

Table altered.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
--------------------------------------------------------
select "A","B","C" from T

SQL>
SQL> select * from v;
select * from v
              *
ERROR at line 1:
ORA-04063: view "MCDONAC.V" has errors

You need to recreate the view without the offending column

Anubha Awadhiya, October 24, 2016 - 4:58 am UTC

Thanks Connor, its really useful to know that view will consider table definition at the time of view definition only, even if we use *.

October 24, 2016 - 6:29 am UTC

glad we could help

A reader, November 04, 2017 - 8:25 am UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Result of view, if base table is modified

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs