rem $DBA/crviews.sql
rem
rem This script generates another script that will include all the CREATE
rem VIEW statements for those in the database (except for the SYS user).
rem
rem This script must be run by a user with the DBA role under Oracle7.
rem
rem Created 10/21/96 by Brian Lomasky
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
select 'Creating view build script...' from dual;
create table view_temp (	lineno NUMBER,
				text VARCHAR2(80));

declare
	cursor view_cursor is select
		owner,
		view_name,
		text
		from sys.dba_views
		where owner != 'SYS'
		order by owner, view_name;
	lv_owner		sys.dba_views.owner%TYPE;
	lv_view_name		sys.dba_views.view_name%TYPE;
	lv_text			sys.dba_views.text%TYPE;
	lv_lineno		number;
	text_length		number;
	startp			number;
	xchar			number;
	break_pos		number;
	lf_pos			number;
	semi_pos		number;
	lf_break		number;
	backwords		number;
	new_line		number;
	offset			number;
	out_start		number;
	out_len			number;
	l			number;
	out_line		varchar2(2000);
	bef_chars		varchar2(2000);
	a_lin			varchar2(2000);
	my_lin			varchar2(2000);
	search_for_break	boolean;
	start_break_search	number;

	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 view_temp values (lv_lineno, x_lin);
			if x_force = 0
			then
				return x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into view_temp values (lv_lineno, x_str);
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into view_temp values (
					lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;

	function brkline(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		my_lin := x_lin;
		text_length := nvl(length(x_str), 0);
		-- Break long text field into smaller groups to print
		startp := 1;
		while startp <= text_length
		loop
			-- Assume not backing up to first space
			backwords := 0;
			offset := 0;
			-- Assume forcing a new line
			new_line := 1;
			-- Break line at next space/tab, semi-colon, linefeed,
			-- 80 chars, or end of string
			search_for_break := TRUE;
			start_break_search := startp;
			while search_for_break
			loop
				search_for_break := FALSE;
				break_pos := instr(x_str, ' '||chr(9),
					start_break_search);
				-- Search for next space/tab if no characters
				-- come before it
				if break_pos > 0
				then
					bef_chars := ltrim(substr(x_str,
						start_break_search,
						break_pos - start_break_search +
						1));
					if nvl(bef_chars, '@@xyzzy') = '@@xyzzy'
					then
						break_pos := 0;
						if start_break_search + 2 <
							text_length
						then
							search_for_break :=
							    TRUE;
							start_break_search :=
							    start_break_search
							    + 1;
						end if;
					end if;
				end if;
			end loop;
			lf_pos := instr(x_str, chr(10), startp);
			lf_break := 0;
			-- See if a linefeed break comes before space/tab break
			if (lf_pos < break_pos or break_pos = 0) and lf_pos > 0
			then
				-- Use linefeed as break position search
				break_pos := lf_pos;
				lf_break := 1;
			end if;
			semi_pos := instr(x_str, ';', startp);
			if break_pos + lf_pos = 0 or (break_pos > semi_pos and
				semi_pos > 0)
			then
				if semi_pos = 0
				then
					-- Break line at 80 chars
					break_pos := startp + 80;
					if break_pos > text_length
					then
						break_pos := text_length + 1;
					end if;
					-- Back up to first space
					backwords := 1;
					-- Do not force new line
					new_line := 0;
				else
					-- Assume line breaking at semi-colon
					break_pos := semi_pos + 1;
				end if;
			else
				if lf_break = 0 then
					-- Assume line breaking at space/tab
					break_pos := break_pos + 1;
					-- Skip space part of the space/tab
					offset := 1;
				else
					-- Assume line breaking before linefeed
					-- Skip linefeed
					offset := 1;
				end if;
			end if;
			if break_pos - startp > 80
			then
				break_pos := startp + 79;
				if break_pos > text_length
				then
					break_pos := text_length + 1;
				end if;
				-- Back up to first space
				backwords := 1;
			end if;
			while backwords = 1
			loop
				if break_pos > text_length
				then
					backwords := 0;
					exit;
				end if;
				if break_pos <= startp
				then
					break_pos := startp + 79;
					if break_pos > text_length
					then
						break_pos := text_length + 1;
					end if;
					backwords := 0;
					exit;
				end if;
				if substr(x_str, break_pos, 1) = ' '
				then
					backwords := 0;
					exit;
				end if;
				break_pos := break_pos - 1;
			end loop;
			xchar := break_pos - startp;
			if xchar = 0 then
				if offset = 0 then
					return my_lin;
				end if;
			else
				-- Replace all tabs with 8 spaces in output line
				out_line := replace(substr(x_str, startp,
					xchar), chr(9), '        ');
				-- In case replacing the tabs with spaces caused
				-- the output line to exceed 80 chars, write
				-- 80-col pieces of the translated output line
				out_start := 1;
				l := length(out_line);
				if nvl(l, -1) = -1 then
					return my_lin;
				end if;
				while out_start <= l
				loop
					-- Write output line
					if l >= out_start + 79 then
						out_len := 80;
					else
						out_len := l - out_start + 1;
					end if;
					my_lin := wri(my_lin,
						substr(out_line, out_start,
						out_len), new_line);
					out_start := out_start + out_len;
				end loop;
			end if;
			startp := startp + xchar + offset;
		end loop;
		return my_lin;
	end brkline;

begin
	a_lin := '';
	lv_lineno := 0;
	open view_cursor;
	loop
		fetch view_cursor into
			lv_owner,
			lv_view_name,
			lv_text;
		exit when view_cursor%NOTFOUND;
		a_lin := wri(a_lin, 'create view ' || lv_owner, 0);
		a_lin := wri(a_lin, '.' || lv_view_name, 0);
		a_lin := wri(a_lin, ' as ', 1);
		a_lin := brkline(a_lin, lv_text, 0);
		a_lin := wri(a_lin, ';', 1);
	end loop;
	close view_cursor;
	commit;
exception
	when others then
		rollback;
		raise_application_error(-20000,
			'Unexpected error on ' || lv_owner ||
			'.' || lv_view_name || ': ' ||
			to_char(SQLCODE) || ' - Aborting...');
end;
/

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