Re: How can view keep columns from dictionary

From: William Robertson <william_at_williamrobertson.net>
Date: Fri, 17 Jul 2020 08:56:59 +0100
Message-Id: <47B96942-D334-434D-908C-E879EC26185E_at_williamrobertson.net>

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] 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 - 09:56:59 CEST