RE: How can view keep columns from dictionary

From: Mark W. Farnham <>
Date: Thu, 16 Jul 2020 20:25:32 -0400
Message-ID: <003701d65bd0$c894d2d0$59be7870$>


Depending on how often you do this, that looks like a major drag on concurrency beginning with making the data dictionary volatile.  

Without looking I’m trying to remember if there is a date last modified on the dictionary for tables and views (not the last time the contents were changed, the last time the definition changed.) IF that’s right, then only run the column check when the view definition is older than the table definition (or just skip the column check and re-create the view if the view is older than the table. yeah, that’s the ticket.      

From: [] On Behalf Of Eriovaldo Andrietta Sent: Thursday, July 16, 2020 7:42 PM
To: Jacek Gębal
Cc: Stephen Miller; ORACLE-L
Subject: Re: How can view keep columns from dictionary  

Tks for answer.

I following the way to re-create the view object.  

I will identify it and execute the script to re-create.  

v_sql varchar2(4000);
v_count number:=0;
  for l in (select * from

               my_configuration tab
      v_sql := 'select count(1) into :count from (
                select column_name from all_tab_columns where table_name = ' || chr(39) || l.name_target  || chr(39) || '
                select column_name from all_tab_columns where table_name = ' || chr(39) || l.name_source || chr(39) || ')';
      execute immediate v_sql into v_count;
      if v_count > 0 then 
         dbms_output.put_line ('Difference :  ' || v_count || ' - ' || l.name_target);
      end if;                

  end loop;

with this result i will automate the re-create view.  

Tks a lot.


Em qui., 16 de jul. de 2020 às 19:55, Jacek Gębal <> escreveu:

You could use polymorphic table function to achieve this type of transformation probably (18c and above).  

PTF will return all columns of input table.  

This is however more complicated, less elegant and less performant than views.  

You will need to recreate views on column addition/removal/rename.  


On Thu, 16 Jul 2020, 02:02 Eriovaldo Andrietta, <> wrote:

continuing :

and always retrieve all columns from the table, without know if was added or dropped columns from a table.  

I can create a configuration where I have the main table , all others tables used in the from and all filters that I need, and do not create a view.

I mount the statement sql and execute it.

I think , for now, that it is a way to retrieve "all current columns" from a table.

I cannot use directly the table, because I have filters and restrictions.  

Another mentioned way is re-run the script. I will evaluate both possibilities.    

Many Thanks and Regards.


Em qua., 15 de jul. de 2020 às 21:49, Eriovaldo Andrietta <> escreveu:

Hi all,

Thanks for answering my questiocoluns.

I have a code that extracts data from a schema using views.

So, I have queries like that :
select * from table_a;

and also have views like that:
select x.* from table_x, table_y where = y.id_column;

and in both cases, always I need extract all_columns from a table.

This is what I need.  

Based in the answers, I got that I need a dynamic SQL and not a view to extract the data.

With dynamic sql I can construct the query and always  

Em qua., 15 de jul. de 2020 às 20:18, Stephen Miller <> escreveu:

Always remember that the '*' in a 'SELECT *' is just short hand for the list of currently defined columns in a table or query request.

In this case, when you created the view, ORACLE immediately translated 'SELECT * FROM test' to 'SELECT col1, col2 from test' under the covers.

When you added a new column, the view is still stuck at 'SELECT col1, col2 from test'.

The only way to get the new column into the view is to rebuild the view -- 'CREATE OR REPLACE VIEW vtest AS SELECT * FROM test'

Stephen Miller - email: miller_stephen at

  • Original Message ------ Received: 05:26 PM EDT, 07/15/2020 From: Eriovaldo Andrietta <> To: ORACLE-L <> Subject: How can view keep columns from dictionary

> Hello,
> I have a doubt about this code :
> drop table test;
> create table test
> (col1 number,
> col2 number);
> create view vtest
> as
> select * from test;
> select * from vtest; -- 2 columns
> alter table test add col3 number; -- here table has 3 columns
> select * from vtest; -- view keeps using 2 columns
> Is there a way to retrieve the current columns from table when created view
> as select * from table withou change the view ?
> Regards
> Eriovaldo

Received on Fri Jul 17 2020 - 02:25:32 CEST

Original text of this message