Friday, June 16, 2006

Procedure to rename constraints

set serveroutput on format wrapped size 1000000
/
drop table tst
/
create table tst
(val char(2),
tbl varchar2(30))
/
create or replace procedure proc_constraint_err_hndlr_v1
/*********************************************************************************************************
PROCEDURE NAME: proc_constraint_err_hndlr_v1

DESCRIPTION: Stored procedure that handles errors for proc_constraint_renamer_v8.
RETURN VALUES: N/A
*********************************************************************************************************/
(p_table_name user_constraints.table_name%type,
p_constraint_type user_constraints.constraint_type%type)
is
e_name_not_exst Exception;
e_name_exists Exception;
PRAGMA EXCEPTION_INIT(e_name_not_exst,-23292);
PRAGMA EXCEPTION_INIT(e_name_exists,-02264);
l_sql varchar2(130);
new_name varchar2(50);
r_max number;
l_rownum number;
l_constraint_name user_constraints.constraint_name%type;
l_constraint_type user_constraints.constraint_type%type;
l_table_name user_constraints.table_name%type;
l_generated user_constraints.generated%type;
l_instr_return number;
l_min_length number;
l_tmp_val char(2);
cursor c_max_row_num is
select max(rownum) from user_constraints
where
upper(table_name)=upper(p_table_name)
and
upper(constraint_type)=upper(p_constraint_type);
cursor c2 is
select rownum,constraint_name,constraint_type,table_name,generated
from user_constraints
where
upper(table_name)=upper(p_table_name)
and
upper(constraint_type)=upper(p_constraint_type);


begin
if
p_constraint_type='C' then l_tmp_val:='CK';
elsif
p_constraint_type='P' then l_tmp_val:='PK';
elsif
p_constraint_type='U' then l_tmp_val:='AK';
else
l_tmp_val:='FK';

end if;
open c_max_row_num;
fetch c_max_row_num into r_max;

open c2;
loop
r_max:=r_max+1;
fetch c2 into l_rownum,l_constraint_name,l_constraint_type,l_table_name,l_generated;
new_name:=p_table_name||'_'||l_tmp_val||(r_max+1);
if length(new_name)>30 then
l_min_length:=length(new_name)-30;
new_name:=substr(new_name,1,l_min_length)||'_'||l_tmp_val||(r_max+1);
else
new_name:=p_table_name||'_'||l_tmp_val||(r_max+1);
end if;
if upper(new_name)=upper(l_constraint_name) then
insert into tst values('OK',l_table_name);
else
insert into tst values('NO',l_table_name);
l_sql:='alter table'||' '||l_table_name||' '||'rename constraint'||' '||l_constraint_name||' '||'to'||' '||new_name;
execute immediate(l_sql);
end if;
exit when c2%notfound;

end loop;
close c2;
close c_max_row_num;

exception
when
e_name_not_exst then
dbms_output.put_line('new name'||' '||new_name||' '||sqlerrm||' '||sqlcode);
when e_name_exists then
dbms_output.put_line('already !!'||' '||new_name||' '||sqlerrm||' '||sqlcode);

when others then
dbms_output.put_line('Other new name'||' '||new_name||' '||sqlerrm||' '||sqlcode);

end proc_constraint_err_hndlr_v1;
/
/***************************************************************************************************/



create or replace procedure proc_constraint_renamer_v8
/*********************************************************************************************************
PROCEDURE NAME: proc_constraint_renamer_v8
DESCRIPTION: Stored procedure that generates the dynamic SQL statements to rename the constraints.
But doesn't change the not noll to "-NN#'.
RETURN VALUES: N/A
*********************************************************************************************************/
(p_table_name user_constraints.table_name%type,
p_constraint_type user_constraints.constraint_type%type)
is
e_name_not_exst Exception;
e_name_exists Exception;
PRAGMA EXCEPTION_INIT(e_name_not_exst,-23292);
PRAGMA EXCEPTION_INIT(e_name_exists,-02264);
l_sql varchar2(130);
new_name varchar2(50);
l_rownum number;
l_constraint_name user_constraints.constraint_name%type;
l_constraint_type user_constraints.constraint_type%type;
l_table_name user_constraints.table_name%type;
l_generated user_constraints.generated%type;
l_instr_return number;
l_min_length number;
l_tmp_val char(2);
cursor c2 is
select rownum,constraint_name,constraint_type,table_name,generated
from user_constraints
where
upper(table_name)=upper(p_table_name)
and
upper(constraint_type)=upper(p_constraint_type);
begin
if
p_constraint_type='C' then l_tmp_val:='CK';
elsif
p_constraint_type='P' then l_tmp_val:='PK';
elsif
p_constraint_type='U' then l_tmp_val:='AK';
else
l_tmp_val:='FK';

end if;

open c2;
loop
fetch c2 into l_rownum,l_constraint_name,l_constraint_type,l_table_name,l_generated;
new_name:=p_table_name||'_'||l_tmp_val||l_rownum;
if length(new_name)>30 then
l_min_length:=length(new_name)-30;
new_name:=substr(new_name,1,l_min_length)||'_'||l_tmp_val||l_rownum;
else
new_name:=p_table_name||'_'||l_tmp_val||l_rownum;
end if;
if upper(new_name)=upper(l_constraint_name) then
insert into tst values('OK',l_table_name);
else
insert into tst values('NO',l_table_name);
l_sql:='alter table'||' '||l_table_name||' '||'rename constraint'||' '||l_constraint_name||' '||'to'||' '||new_name;
execute immediate(l_sql);
end if;
exit when c2%notfound;

end loop;
close c2;
exception
when
e_name_not_exst then
dbms_output.put_line('new name'||' '||new_name||' '||sqlerrm||' '||sqlcode);
when e_name_exists then
dbms_output.put_line('already !!'||' '||new_name||' '||sqlerrm||' '||sqlcode);
proc_constraint_err_hndlr_v1(l_table_name,l_constraint_type);
when others then
dbms_output.put_line('Other new name'||' '||new_name||' '||sqlerrm||' '||sqlcode);
end proc_constraint_renamer_v8;
/
/***********************************************************************************************************/
create or replace procedure proc_script_populator_v1
/*********************************************************************************************************
PROCEDURE NAME: proc_script_populator_v1

DESCRIPTION: Stored procedure that calls proc_constraint_renamer_v8.
RETURN VALUES: N/A
*********************************************************************************************************/
is
e_name_exists Exception;
e_other_err Exception;
PRAGMA EXCEPTION_INIT(e_name_exists,-02264);
l_table_name user_constraints.table_name%type;
l_constraint_type user_constraints.constraint_type%type;
l_statement varchar2(2000);
l_chr char(1);
cursor c1 is
select table_name,constraint_type from user_constraints;
begin

l_chr:='''';
open c1;
loop
fetch c1 into l_table_name,l_constraint_type;
l_statement:='begin proc_constraint_renamer_v8('||l_chr||l_table_name||l_chr||','||l_chr||l_constraint_type||l_chr||');
end;';
execute immediate(l_statement);
exit when c1%notfound;
end loop;
close c1;
dbms_output.put_line('procedure proc_script_populator_v1 ran successfully .');
EXCEPTION
when e_name_exists then
dbms_output.put_line('error with table '||l_table_name);
when e_other_err then
dbms_output.put_line(sqlcode||' '||sqlerrm);
when others then
dbms_output.put_line('other error # '||sqlcode||' '||sqlerrm);
end proc_script_populator_v1;
/
exec proc_script_populator_v1;
/

/
/*
Creates a temporary table to hold the constraint information as search_condition column
has "long" data type hence could not be used with the where clause of any query.
*/
drop table tbl_nn
/
create table tbl_nn
(table_name VARCHAR2(30),
constraint_name VARCHAR2(30),
SEARCH_CONDITION varchar2(1000))
/
create or replace procedure nn_const_ph1_v1
/*********************************************************************************************************
PROCEDURE NAME: nn_const_ph1_v1
PROJECT NAME:

DESCRIPTION: First of the two Stored procedures that handles Not Null constraints renaming.
RETURN VALUES: N/A
*********************************************************************************************************/
is
out_str varchar2(10);
l_table_name user_constraints.table_name%type;
l_constraint_name user_constraints.constraint_name%type;
l_SEARCH_CONDITION varchar2(1000);

cursor c1 is
select table_name,constraint_name,SEARCH_CONDITION from user_constraints where
constraint_type='C';

begin

open c1;
loop
fetch c1 into l_table_name,l_constraint_name,l_SEARCH_CONDITION;
if l_SEARCH_CONDITION like '%NOT NULL%' then
out_str:='not null';
insert into tbl_nn values(l_table_name,l_constraint_name,l_SEARCH_CONDITION);
else
out_str:='others';

end if;
exit when c1%notfound;
end loop;
close c1;
dbms_output.put_line('procedure nn_const_ph1_v1 ran successfully .');
end;
/
exec nn_const_ph1_v1;
/
create or replace procedure nn_cons_ph2_v1
/*********************************************************************************************************
PROCEDURE NAME: nn_const_ph2_v1
PROJECT NAME:
DESCRIPTION: Second of the two Stored procedures that handles Not Null constraints renaming.
RETURN VALUES: N/A
*********************************************************************************************************/
is
e_nonexistant_constraint_1 Exception;
e_nonexistant_constraint_2 Exception;
PRAGMA EXCEPTION_INIT(e_nonexistant_constraint_1,-23292);
PRAGMA EXCEPTION_INIT(e_nonexistant_constraint_2,-06512);
l_table_name tbl_nn.table_name%type;
l_constraint_name tbl_nn.constraint_name%type;
l_sql varchar(500);
new_name varchar2(500);
cursor c1 is

select table_name,constraint_name,substr(constraint_name,1,LENGTH(CONSTRAINT_NAME)-3)||'NN'||substr(constraint_name,-1,1) from tbl_nn;

begin

open c1;
loop
fetch c1 into l_table_name,l_constraint_name,new_name;

l_sql:='alter table'||' '||l_table_name||' '||'rename constraint'||' '||l_constraint_name||' '||'to '||new_name;
exit when c1%notfound;
execute immediate (l_sql);
end loop;
close c1;
dbms_output.put_line('procedure nn_cons_ph2_v1 ran successfully .');
EXCEPTION
when e_nonexistant_constraint_1 then
dbms_output.put_line('e_nonexistant_constraint_1 '||sqlcode||' '||sqlerrm);
when e_nonexistant_constraint_2 then
dbms_output.put_line('e_nonexistant_constraint_2 '||sqlcode||' '||sqlerrm);
when others then
dbms_output.put_line(' other '||sqlcode||' '||sqlerrm);
end;
/
exec nn_cons_ph2_v1;
/
drop table tbl_nn
/
drop table tst
/

No comments:

Post a Comment