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.
Jacek
On Thu, 16 Jul 2020, 02:02 Eriovaldo Andrietta, <ecandrietta@xxxxxxxxx>
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.
Eriovaldo
Em qua., 15 de jul. de 2020 às 21:49, Eriovaldo Andrietta <
ecandrietta@xxxxxxxxx> 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 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@xxxxxxx> 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@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: How can view keep columns from dictionary
Hello,view
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
as select * from table withou change the view ?
Regards
Eriovaldo
