Re: How can view keep columns from dictionary

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Wed, 15 Jul 2020 22:00:56 -0300
Message-ID: <CAJdDhaODdMjcD6KnxgEJ-33QTTRMpaqpubWawJ7HY0H3pMWHgg_at_mail.gmail.com>



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
Received on Thu Jul 16 2020 - 03:00:56 CEST

Original text of this message