Re: How can view keep columns from dictionary

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_at_gmail.com> 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_at_gmail.com> 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_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