Re: How can view keep columns from dictionary

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 x.id = 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 <miller_stephen_at_usa.net> 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 usa.net
>
>
> ------ Original Message ------
> Received: 05:26 PM EDT, 07/15/2020
> From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
> To: ORACLE-L <oracle-l_at_freelists.org>
> 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
> >
>
>
>

--
http://www.freelists.org/webpage/oracle-l