RE: View Creation script?

Thanks Jared!  

Jay Miller
Sr. Oracle DBA
201.369.8355  

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Thursday, August 09, 2007 4:34 PM
To: Miller, Jay
Cc: oracle-l_at_freelists.org
Subject: 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