Re: How can view keep columns from dictionary

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Thu, 16 Jul 2020 20:41:52 -0300
Message-ID: <CAJdDhaNf8_WGam-X8F2=rjUv9=Xxx=+E8C+EYCbyfKMvyZ55Wg_at_mail.gmail.com>

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 - 01:41:52 CEST