rem $DBA/crprofs.sql
rem
rem This script generates another script that will include all the CREATE
rem PROFILE statements for those in the database.
rem
rem This script must be run by a user with the DBA role under Oracle7.
rem
rem Assumes that there is an OPS$ account for the user running this script.
rem
rem Created 09/09/97 by Brian Lomasky
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
select 'Creating profile build script...' from dual;

create table prof_temp (	lineno NUMBER,
				text VARCHAR2(80));

declare
	cursor prof_cursor is select
		profile,
		resource_name,
		limit
		from sys.dba_profiles
		where profile <> 'DEFAULT'
		order by 1, 2;

	lv_profile		sys.dba_profiles.profile%TYPE;
	lv_resource_name	sys.dba_profiles.resource_name%TYPE;
	lv_limit		sys.dba_profiles.limit%TYPE;
	lv_lineno		number;
	a_lin			varchar2(80);
	prev_profile		sys.dba_profiles.profile%TYPE;

	function wri(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lv_lineno := lv_lineno + 1;
			insert into prof_temp values (lv_lineno, x_lin);
			if x_force = 0
			then
				return '    '||x_str;
			else
				lv_lineno := lv_lineno + 1;
				if substr(x_lin,1,2) = '  '
				then
					insert into prof_temp values (
						lv_lineno, x_str);
				else
					insert into prof_temp values (
						lv_lineno, '    '||x_str);
				end if;
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into prof_temp values (
					lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;

begin
	a_lin := '';
	prev_profile := '@';
	open prof_cursor;
	loop
		fetch prof_cursor into
			lv_profile,
			lv_resource_name,
			lv_limit;
		exit when prof_cursor%NOTFOUND;
		if prev_profile = lv_profile then
			a_lin := wri(a_lin, '    ' || lv_resource_name, 0);
			a_lin := wri(a_lin, ' ', 0);
			a_lin := wri(a_lin, lv_limit, 1);
		else
			if prev_profile != '@' then
				a_lin := wri(a_lin, ';', 1);
			end if;
			a_lin := wri(a_lin, 'CREATE PROFILE ', 0);
			a_lin := wri(a_lin, lv_profile, 0);
			a_lin := wri(a_lin, ' limit', 1);
			a_lin := wri(a_lin, '    ' || lv_resource_name, 0);
			a_lin := wri(a_lin, ' ', 0);
			a_lin := wri(a_lin, lv_limit, 1);
			prev_profile := lv_profile;
		end if;
	end loop;
	close prof_cursor;
	if prev_profile != '@' then
		a_lin := wri(a_lin, ';', 1);
	end if;
end;
/

set termout off
set heading off
spool cr_prof.sql
select 'rem cr_prof.sql' from dual;
select 'rem' from dual;
select 'rem ***** All profiles for database ' || name from v$database;
select 'rem' from dual;
select 'set feedback off' from dual;
select text from prof_temp order by lineno;
spool off
drop table prof_temp;
set termout on
select 'Created cr_prof.sql...' from dual;
set termout off
exit