RE: constraints question
Date: Thu, 30 Apr 2009 16:06:15 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF023CA676_at_EXCNYSM0A1AJ.nysemail.nyenet>
Eugene,
How about this:
declare
l_pk_name varchar2(100);
in_number number := 1;
--===========================================
function get_pk_name(in_table_name in varchar2) return varchar2 is
ret_str varchar2(100);
begin
select constraint_name
into ret_str
from user_constraints
where table_name=in_table_name
and constraint_type='P';
return ret_str;
exception
when others then
return '';
end;
--===========================================
procedure get_cs_name(in_pk_name in varchar2,in_number in out number) is
l_pk_name varchar2(100);
l_number NUMBER := in_number;
l_in_number number := 0;
begin
for c in (select table_name, constraint_name
from user_constraints
where r_constraint_name= in_pk_name
order by table_name) loop
dbms_output.put_line(lpad('>',l_number*2) || ' ' ||
c.table_name); -- || ' ' || c.constraint_name);
l_pk_name := get_pk_name(c.table_name);
if l_pk_name is not null
and l_pk_name <> in_pk_name then
null;
l_in_number := in_number + 1;
get_cs_name(l_pk_name,l_in_number);
end if;
end loop;
- l_number := l_number + 1;
end;
--===========================================
begin
for c in (select table_name from user_tables order by 1) loop
dbms_output.put_line(c.table_name);
l_pk_name := get_pk_name(c.table_name);
get_cs_name(l_pk_name,in_number);
dbms_output.put_line ('================================');
end loop;
end;
/
Tom
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 30 2009 - 15:06:15 CDT