On 8/9/07,
Jay.Miller@tdameritrade.com <
Jay.Miller@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
