PROMPT CREATE OR REPLACE PACKAGE helper_pkg
CREATE OR REPLACE package helper_pkg
as
	type varchar2_duplet is record (a varchar2(30), b varchar2(30));
	function find_table(p_object varchar2) return varchar2_duplet;
	procedure desc_cons(a_owner_table in varchar2, a_with_rep in number default 0);
	procedure desc_inds(a_owner_table in varchar2, a_with_rep in number default 0);
end;
/

PROMPT CREATE OR REPLACE PACKAGE BODY helper_pkg
CREATE OR REPLACE package body helper_pkg
as

	function find_table(p_object in varchar2)
	return varchar2_duplet
	as
		l_object varchar2(30);
		l_point_pos number(2);
		l_exists number(1);
		l_owner_name varchar2(30);
		l_object_name varchar2(30);
		l_object_type varchar2(30);
		l_duplet varchar2_duplet;
	begin
		l_object := Upper(Trim(p_object));
		l_point_pos := Instr(l_object, '.');
		if l_point_pos = 0 then
			select count(*)
			into l_exists
			from dual
			where
				exists (
					select null
					from all_synonyms
					where
						owner = user
						and synonym_name = l_object
				)
			;
			if l_exists = 1 then
				select table_owner
				into l_owner_name
				from all_synonyms
				where
					owner = user
					and synonym_name = l_object
				;
			end if;
			l_exists := 0;
			select count(*)
			into l_exists
			from dual
			where
				exists (
					select null
					from all_tables
					where
						owner = user
						and table_name = l_object
				)
			;
			if l_exists = 1 then
				select owner
				into l_owner_name
				from all_tables
				where
					owner = user
					and table_name = l_object
				;
			end if;
			l_object_name := l_object;
		else
			l_owner_name := Substr(l_object, 1, l_point_pos - 1);
			l_object_name := Substr(l_object, l_point_pos + 1);
		end if;
		l_duplet.a := l_owner_name;
		l_duplet.b := l_object_name;
		return l_duplet;
	exception
		when others then
			dbms_output.put_line('ERROR IN HELPER_PKG.FIND_TABLE');
	end;

	procedure desc_cons(a_owner_table in varchar2, a_with_rep in number default 0)
	as
		type tab_info_row is record(
			constraint_name char(30),
			constraint_type char(1),
			position number(2),
			column_name char(30),
			reference char(62)
		);
		type tab_info is table of tab_info_row index by binary_integer;
		p_ti tab_info;
		p_counter number(8) default 0;
		p_constraint_name char(30);
		p_owner varchar2(30);
		p_table_name varchar2(30);
		p_duplet varchar2_duplet;
	begin
		p_duplet := find_table(a_owner_table);
		if p_duplet.a is null then
			dbms_output.put_line(lpad('-', 120, '-'));
			dbms_output.put_line('No such table: ' || p_duplet.b);
			dbms_output.put_line(lpad('-', 120, '-'));
			return;
		else
			null;
			dbms_output.put_line(lpad('-', 120, '-'));
			dbms_output.put_line(p_duplet.a || '.' || p_duplet.b || ' CONSTRAINTS');
			dbms_output.put_line(lpad('-', 120, '-'));
		end if;
		p_owner := p_duplet.a;
		p_table_name := p_duplet.b;
		for x in (
			select
				subA.constraint_name,
				subA.constraint_type,
				subA.position,
				subA.column_name,
				subB.table_name || '(' || subB.column_name || ')' reference,
				subA.owner,
				subA.r_owner
			from (
					select
						owner,
						constraint_name,
						table_name,
						ac.constraint_type,
						acc.position,
						acc.column_name,
						ac.r_owner,
						ac.r_constraint_name
					from
						all_constraints ac
						join all_cons_columns acc using(owner, constraint_name, table_name)
				) subA
				left join (
					select
						owner,
						constraint_name,
						table_name,
						acc.position,
						acc.column_name
					from
						all_constraints ac
						join all_cons_columns acc using(owner, constraint_name, table_name)
				) subB on
					subA.r_owner = subB.owner and
					subA.r_constraint_name = subB.constraint_name and
					subA.position = subB.position
			where
				subA.owner = p_owner and
				subA.table_name = p_table_name and
				subA.constraint_type != 'C'
			order by subA.constraint_type, subA.constraint_name, subA.position
		) loop
			if (a_with_rep = 0) and (x.r_owner != x.owner) then
				goto CONTINUE;
			end if;
			p_counter := p_counter + 1;
			p_ti(p_counter).constraint_name := x.constraint_name;
			p_ti(p_counter).constraint_type := x.constraint_type;
			p_ti(p_counter).position := x.position;
			p_ti(p_counter).column_name := x.column_name;
			p_ti(p_counter).reference := x.reference;
			<<CONTINUE>>
			null;
		end loop;
		if p_ti.count = 0 then
			dbms_output.put_line(lpad('-', 120, '-'));
			dbms_output.put_line('Table ' || p_owner || '.' || p_table_name || ' have no constraints');
			dbms_output.put_line(lpad('-', 120, '-'));
			return;
		end if;
		for ind in p_ti.first..p_ti.last loop
			if p_constraint_name = p_ti(ind).constraint_name then
				p_ti(ind).constraint_name := '-';
				p_ti(ind).constraint_type := '-';
			else
				p_constraint_name := p_ti(ind).constraint_name;
				dbms_output.put_line(lpad('-', 120, '-'));
			end if;
			dbms_output.put_line(
				p_ti(ind).constraint_name || ' ' ||
				p_ti(ind).constraint_type || ' ' ||
				Nvl(p_ti(ind).position, 1) || ' ' ||
				p_ti(ind).column_name || ' ' ||
				p_ti(ind).reference
			);
		end loop;
		dbms_output.put_line(lpad('-', 120, '-'));
	exception
		when others then
			dbms_output.put_line('ERROR IN HELPER_PKG.DESC_CONS');
	end;

	procedure desc_inds(a_owner_table in varchar2, a_with_rep in number default 0)
	as
		type tab_info_row is record(
			index_name char(30),
			column_position number(2),
			column_name char(30)
		);
		type tab_info is table of tab_info_row index by binary_integer;
		p_ti tab_info;
		p_counter number(8) default 0;
		p_index_name char(30);
		p_owner varchar2(30);
		p_table_name varchar2(30);
		p_duplet varchar2_duplet;
	begin
		p_duplet := find_table(a_owner_table);
		if p_duplet.a is null then
			dbms_output.put_line(lpad('-', 120, '-'));
			dbms_output.put_line('No such table: ' || p_duplet.b);
			dbms_output.put_line(lpad('-', 120, '-'));
			return;
		else
			dbms_output.put_line(lpad('-', 120, '-'));
			dbms_output.put_line(p_duplet.a || '.' || p_duplet.b || ' INDEXES');
			dbms_output.put_line(lpad('-', 120, '-'));
		end if;
		p_owner := p_duplet.a;
		p_table_name := p_duplet.b;
		for x in (
			select
				index_name,
				column_position,
				column_name
			from all_ind_columns
			where
				index_owner = p_owner
				and table_owner = p_owner
				and table_name = p_table_name
			order by index_name, column_position
		) loop
			p_counter := p_counter + 1;
			p_ti(p_counter).index_name := x.index_name;
			p_ti(p_counter).column_position := x.column_position;
			p_ti(p_counter).column_name := x.column_name;
			<<CONTINUE>>
			null;
		end loop;
		if p_ti.count = 0 then
			dbms_output.put_line(lpad('-', 120, '-'));
			dbms_output.put_line('Table ' || p_owner || '.' || p_table_name || ' have no indexes');
			dbms_output.put_line(lpad('-', 120, '-'));
			return;
		end if;
		for ind in p_ti.first..p_ti.last loop
			if p_index_name = p_ti(ind).index_name then
				p_ti(ind).index_name := '-';
				p_ti(ind).index_name := '-';
			else
				p_index_name := p_ti(ind).index_name;
				dbms_output.put_line(lpad('-', 120, '-'));
			end if;
			dbms_output.put_line(
				p_ti(ind).index_name || ' ' ||
				Nvl(p_ti(ind).column_position, 1) || ' ' ||
				p_ti(ind).column_name
			);
		end loop;
		dbms_output.put_line(lpad('-', 120, '-'));
	exception
		when others then
			dbms_output.put_line('ERROR IN HELPER_PKG.DESC_INDS');
	end;

end;
/