Re: View Creation script?

On 8/9/07, Jay.Miller_at_tdameritrade.com <Jay.Miller_at_tdameritrade.com> wrote:
>
> Before I write one, does anyone have a script handy (or know of one
> online) that will generate the DDL for a view that matches the underlying
> table? I need to create a bunch of views that will differ only slightly
> from their underlying table and am looking for ways to speed it up...
>
>

Here's a start:

define tab_owner='PERFSTAT'

declare

   cursor tables (tab_owner_in varchar2)    is
   select owner,table_name
   from dba_tables
   where owner = upper(tab_owner_in);

   cursor tabcols ( tab_owner_in varchar2, table_name_in varchar2)    is
   select column_name
   from dba_tab_columns
   where owner = upper(tab_owner_in)
   and table_name = upper(table_name_in)    order by column_id;

   is_first boolean;

begin

   for tabrec in tables('&&tab_owner')
   loop

      dbms_output.put_line('create or replace view ' || tabrec.table_name || '_v as ');

      dbms_output.put_line('select');
      is_first := true;
      for colrec in tabcols(tabrec.owner,tabrec.table_name)
      loop
         if is_first then
            is_first := false;
            dbms_output.put(chr(9));
         else
            dbms_output.put(chr(9)||', ');
         end if;
         dbms_output.put_line(colrec.column_name);
      end loop;
      dbms_output.put_line('from ' || tabrec.table_name);
      dbms_output.put_line('/');
      dbms_output.put_line('--==============================');

   end loop;

end;
/

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l