split_use_derive.sql

-- -----------------------------------------------------------------------------
--                 WWW.PETEFINNIGAN.COM LIMITED
-- -----------------------------------------------------------------------------
-- Script Name : profiles.sql
-- Author      : Pete Finnigan
-- Date        : November 2007, 2009
-- -----------------------------------------------------------------------------
-- Description : Use this script to give an overview of the profile settings 
--               for each user in the dataabse. 
-- -----------------------------------------------------------------------------
-- Maintainer  : Pete Finnigan (http://www.petefinnigan.com)
-- Copyright   : Copyright (C) 2007, 2009 PeteFinnigan.com Limited. All rights
--               reserved. All registered trademarks are the property of their
--               respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
-- License     : This software is free software BUT it is not in the public
--               domain. This means that you can use it for personal or 
--               commercial work but you cannot remove this notice or copyright
--               notices or the banner output by the program or edit them in any
--               way at all. You also cannot host/distribute/copy or in anyway 
--               make this script available through any means either in original
--               form or any derivitive work based on it. The script is 
--               only available from its own webpage 
--               http://www.petefinnigan.com/split_user_derive.sql or any other 
--               page that PeteFinnigan.com Limited hosts it from.
--               This script cannot be incorporated into any other free or 
--               commercial tools without permission from PeteFinnigan.com 
--               Limited.
--
--               In simple terms use it for free but dont make it available in
--               any way or build it into any other tools. 
-- -----------------------------------------------------------------------------
-- Version History
-- ===============
--
-- Who         version     Date      Description
-- ===         =======     ======    ======================
-- P.Finnigan  1.0         Nov 2007  First Issue.
-- P.Finnigan  1.1         Sep 2009  Update for Oak Table book
-- -----------------------------------------------------------------------------
--whenever sqlerror exit rollback

set feed off
spool split_use_derive_output.sql

declare
	lv_first boolean:=true;
	lv_second boolean:=true;
	cursor c_main is
	select distinct user_name
	from sys.default_pwd$;	
begin
	dbms_output.put_line('select username,''BIN'' from dba_users');
	dbms_output.put_line('where username in (');
	for lv_main in c_main loop
		if(lv_first) then
			dbms_output.put_line(''''||lv_main.user_name||'''');
			lv_first:=false;
		else
			dbms_output.put_line(','''||lv_main.user_name||'''');
		end if;
	end loop;
	dbms_output.put_line(') union ');
	dbms_output.put_line('select username,''---'' from dba_users');
	dbms_output.put_line('where username not in (');
	for lv_main in c_main loop
		if(lv_second) then
			dbms_output.put_line(''''||lv_main.user_name||'''');
			lv_second:=false;
		else
			dbms_output.put_line(','''||lv_main.user_name||'''');
		end if;
	end loop;
	dbms_output.put_line(');');
end;
/

spool off
set feed on