ORACLE-View containing ’select * from table‘

A view containing ’select * from table‘ takes/selects only the attributes from the creation-time.
Attributes added afterwards to the base-table are not considered in the view anymore!
The view stays valid.

drop view viewselectstern;
drop table tableselectstern;

create table tableselectstern
(
id number,
inhalt1 varchar2 (50),
inhalt2 varchar2 (50),
inhalt3 varchar2 (50)
);
insert into tableselectstern values (1,’eins‘,’zwei‘,’drei‘);
commit;

create or replace view viewselectstern as select * from tableselectstern;

select table_name,column_name,column_id from user_tab_cols where table_name = ‚TABLESELECTSTERN‘ order by column_id;
/*
TABLE_NAME COLUMN_NAME COLUMN_ID
TABLESELECTSTERN ID 1
TABLESELECTSTERN INHALT1 2
TABLESELECTSTERN INHALT2 3
TABLESELECTSTERN INHALT3 4
*/
select table_name,column_name,column_id from user_tab_cols where table_name = ‚VIEWSELECTSTERN‘ order by column_id;
/*
TABLE_NAME COLUMN_NAME COLUMN_ID
VIEWSELECTSTERN ID 1
VIEWSELECTSTERN INHALT1 2
VIEWSELECTSTERN INHALT2 3
VIEWSELECTSTERN INHALT3 4
*/
select * from viewselectstern;
/*
ID INHALT1 INHALT2 INHALT3
1 eins zwei drei
*/
alter table tableselectstern add inhalt4 varchar2(50);

select * from viewselectstern;
/*
ID INHALT1 INHALT2 INHALT3
1 eins zwei drei
*/

select table_name,column_name,column_id from user_tab_cols where table_name = ‚TABLESELECTSTERN‘;
/*
TABLE_NAME COLUMN_NAME COLUMN_ID
TABLESELECTSTERN ID 1
TABLESELECTSTERN INHALT1 2
TABLESELECTSTERN INHALT2 3
TABLESELECTSTERN INHALT3 4
TABLESELECTSTERN INHALT4 5
*/
select table_name,column_name,column_id from user_tab_cols where table_name = ‚VIEWSELECTSTERN‘ order by column_id;
/*
TABLE_NAME COLUMN_NAME COLUMN_ID
VIEWSELECTSTERN ID 1
VIEWSELECTSTERN INHALT1 2
VIEWSELECTSTERN INHALT2 3
VIEWSELECTSTERN INHALT3 4
*/

Schreibe einen Kommentar