Re: How can view keep columns from dictionary

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