Set serveroutput on size unlimited
Declare
Declare
tab varchar2(50);
owne varchaR2(50);
cursor c2 is select owner,table_name,column_name,column_id,data_type,data_length,data_precision,data_scale from dba_tab_columns where table_name='&tab' and owner='&owne' order by column_id;
v_id number;
Begin
select max(column_id) into v_id from dba_tab_columns where table_name='&tab';
for c2_rec in c2 loop
if c2_rec.column_id=1 and c2_rec.data_type in ('VARCHAR2','CHAR') then
dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_length||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_length||'),');
elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_precision IS NULL then
dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||',');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||',');
elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_scale IS NULL then
dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
elsif c2_rec.column_id=1 and c2_rec.data_type='NUMBER'and c2_rec.data_precision is not null then
dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
elsif c2_rec.column_id=1 and c2_rec.data_type='DATE' then
dbms_output.put_line('CREATE TABLE AUD_'||c2_rec.owner||'.AUD_'||c2_rec.table_name||'(');
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||',');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||',');
elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type in ('VARCHAR2','CHAR')then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_length||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_length||'),');
elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_precision IS NULL then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||',');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||',');
elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_scale IS NULL then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='NUMBER'and c2_rec.data_precision is not null then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
elsif c2_rec.column_id>1 and c2_rec.column_id<v_id and c2_rec.data_type='DATE' then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||',');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||',');
elsif c2_rec.column_id=v_id and c2_rec.data_type in ('VARCHAR2','CHAR')then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_length||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_length||'),');
elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND c2_rec.data_precision IS NULL then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||',');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||',');
elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND c2_rec.data_scale IS NULL then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||'),');
elsif c2_rec.column_id=v_id and c2_rec.data_type='NUMBER'AND c2_rec.data_precision is not null then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||'('||c2_rec.data_precision||','||c2_rec.data_scale||'),');
elsif c2_rec.column_id=1 and c2_rec.data_type='DATE' then
dbms_output.put_line('OLD_'||c2_rec.column_name||' '||c2_rec.data_type||',');
dbms_output.put_line('NEW_'||c2_rec.column_name||' '||c2_rec.data_type||',');
end if;
end loop;
dbms_output.put_line('CHANGE_DATE DATE,');
dbms_output.put_line('CHANGE_BY VARCHAR2(30),');
dbms_output.put_line('OPERATION CHAR(1),');
dbms_output.put_line('ADDRESS VARCHAR2(200));');
end;
No comments:
Post a Comment