--
-- Author: Mohit Dubey
-- Visit http://www.geocities.com/md_seraphin for more goodies!
--
-- This program is distributed under the GNU Public License Version 2
-- with the additional privisio that the original author's name and
-- contact details must be retained as-is in any modified or copied
-- versions of this program.
--

set feedback off

create table genReport
(userid		varchar2(30),
gendate		date	default sysdate,
numtab		number default 0,
numpri		number default 0,
numunique	number default 0,
numfk			number default 0,
numcheck		number default 0,
numuidx		number default 0,
numnuidx		number default 0,
numdefault	number default 0,
numpack		number default 0,
numproc		number default 0,
numfunc		number default 0,
numtrig		number default 0,
numsyn		number default 0,
numlink		number default 0,
numtype		number default 0);

declare
	cursor cur is
		select object_type, count(1)
		from user_objects
		group by object_type
		union
		select constraint_type, count(1)
		from	 user_constraints
		group by constraint_type
		union
		select uniqueness, count(1)
		from   user_indexes
		group by uniqueness;
	v_SqlFragment	varchar2(200);
	v_SqlStatement	varchar2(500);
	v_ObjectType	varchar2(20);
	v_UserId			varchar2(30);
	v_Number			number;
	v_DoUpdate		boolean;
begin
	dbms_output.enable(1000000);
	dbms_output.put_line('Starting table population...');
	--
	-- Get current User and store in Report Table.
	--
	select user
	into	 v_UserId
	from	 dual;
	insert into genReport
		(userid)
	values
		(v_UserId);

	--
	-- Loop the loop!
	--
	open cur;
	fetch cur into v_ObjectType,v_Number;
	loop
		exit when cur%notfound;
		v_DoUpdate := TRUE;
		--
		-- Generate the fragment to update relevant column
		--
		if (v_ObjectType = 'TABLE') then
			v_SqlFragment := 'set	 numtab = :1';
		elsif (v_ObjectType = 'PACKAGE') then
			v_SqlFragment := 'set    numpack = :1';
		elsif (v_ObjectType = 'PROCEDURE') then
			v_SqlFragment := 'set    numproc = :1';
		elsif (v_ObjectType = 'FUNCTION') then
			v_SqlFragment := 'set    numfunc = :1';
		elsif (v_ObjectType = 'TRIGGER') then
			v_SqlFragment := 'set    numtrig = :1';
		elsif (v_ObjectType = 'SYNONYM') then
			v_SqlFragment := 'set    numsyn = :1';
		elsif (v_ObjectType = 'DATABASE LINK') then
			v_SqlFragment := 'set    numlink = :1';
		elsif (v_ObjectType = 'TYPE') then
			v_SqlFragment := 'set    numtype = :1';
		elsif (v_ObjectType = 'P') then
			v_SqlFragment := 'set    numpri = :1';
		elsif (v_ObjectType = 'U') then
			v_SqlFragment := 'set    numunique = :1';
		elsif (v_ObjectType = 'R') then
			v_SqlFragment := 'set    numfk = :1';
		elsif (v_ObjectType = 'C') then
			v_SqlFragment := 'set    numcheck = :1';
		elsif (v_ObjectType = 'UNIQUE') then
			v_SqlFragment := 'set    numuidx = :1';
		elsif (v_ObjectType = 'NONUNIQUE') then
			v_SqlFragment := 'set    numnuidx = :1';
		else
			dbms_output.put_line('Type '||v_ObjectType||' objects ignored.');
			v_DoUpdate := FALSE;
		end if;
		--
		-- Do update!
		--
		if (v_DoUpdate = TRUE) then
			v_SqlStatement := 'update genReport '||v_SqlFragment;
			execute immediate v_SqlStatement using v_Number;
		end if;
		fetch cur into v_ObjectType,v_Number;
	end loop;
	close cur;
	commit;
exception
	when others then
		dbms_output.put_line('  ');
		dbms_output.put_line('**');
		dbms_output.put_line('** Oracle Error encountered. Details below...');
		dbms_output.put_line('**');
		dbms_output.put_line(SQLERRM);
end;
/