Re: How can view keep columns from dictionary

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Fri, 17 Jul 2020 09:01:22 -0300
Message-ID: <CAJdDhaP0c4=dwUDeNMTKc3JVWvEP0K5GJDvxkJMBH==r3M1Jjg_at_mail.gmail.com>



Hello,
Yes, there are lot of ways.
I will choose one of its.
Thanks.
Eriovaldo

Em sex., 17 de jul. de 2020 às 06:02, Jacek Gębal <jgebal_at_gmail.com> escreveu:

> I would go one step further and avoud the SQL in loop.
> begin
> for i in (
> select ... from my_confugiration t
> where 0 > (select count(*) from (select .... minus select))
> )
> --Do stuff here
> End loop;
> End;
> /
>
> On Fri, 17 Jul 2020, 08:59 William Robertson, <
> william_at_williamrobertson.net> wrote:
>
>> You don’t need dynamic SQL for this, so it can be simplified a bit, to
>>
>> declare
>> v_count number := 0;
>> begin
>> dbms_output.enable(null);
>>
>>
>> for l in (
>> select * from my_configuration tab
>> )
>> loop
>> select count(*) into v_count from (
>>
>> select column_name from user_tab_columns where table_name = l.name_target
>> minus
>>
>> select column_name from user_tab_columns where table_name = l.name_source
>> );
>>
>>
>> if v_count > 0 then
>> dbms_output.put_line('Difference: ' || v_count || ' -
>> ' || l.name_target);
>> end if;
>> end loop;
>> end;
>> /
>>
>>
>> Also you should either run it in the target schema and use USER_ views,
>> or else specify the table owner.
>>
>> William
>>
>> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org
>> <oracle-l-bounce_at_freelists.org>] 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.
>>
>> declare
>> v_sql varchar2(4000);
>> v_count number:=0;
>> begin
>> dbms_output.enable(null);
>> for l in (select * from
>> my_configuration tab
>> )
>> loop
>> v_count:=0;
>> 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) || '
>> minus
>> 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;
>> end;
>> /
>> with this result i will automate the re-create view.
>>
>> Tks a lot.
>> Eriovaldo
>>
>>
>>
>> Em qui., 16 de jul. de 2020 às 19:55, Jacek Gębal <jgebal_at_gmail.com>
>> 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.
>>
>> 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
Received on Fri Jul 17 2020 - 14:01:22 CEST

Original text of this message