rem $DBA/crcomms.sql
rem
rem This script generates another script that will include all the COMMENT ON
rem 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/31/96 by Brian Lomasky
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
define q=chr(39)
select 'Creating comment build script...' from dual;
create table comm_temp (
	ownr	varchar2(30),
	tabnam	varchar2(30),
	colnam	varchar2(30),
	lineno	NUMBER,
	text	VARCHAR2(80)
	);

declare
	cursor commt_cursor is select
		owner,
		table_name,
		table_type,
		comments
		from sys.dba_tab_comments
		where owner != 'SYS' and comments is not null
		order by owner, table_name;

	cursor commc_cursor is select
		owner,
		table_name,
		column_name,
		comments
		from sys.dba_col_comments
		where owner != 'SYS' and comments is not null
		order by owner, table_name, column_name;

	lv_owner		sys.dba_tab_comments.owner%TYPE;
	lv_table_name		sys.dba_tab_comments.table_name%TYPE;
	lv_table_type		sys.dba_tab_comments.table_type%TYPE;
	lv_comments		sys.dba_tab_comments.comments%TYPE;
	lv_column_name		sys.dba_col_comments.column_name%TYPE;
	lv_lineno		number;
	text_length		number;
	startp			number;
	xchar			number;
	break_pos		number;
	lf_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(80);
	my_lin			varchar2(2000);

	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 comm_temp values (
				lv_owner,
				lv_table_name,
				lv_column_name,
				lv_lineno,
				x_lin);
			if x_force = 0
			then
				return x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into comm_temp values (
					lv_owner,
					lv_table_name,
					lv_column_name,
					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 comm_temp values (
					lv_owner,
					lv_table_name,
					lv_column_name,
					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, linefeed,
			-- 80 chars, or end of string
			break_pos := instr(x_str, ' '||chr(9), startp);
			-- No break at space/tab if no characters come before it
			if break_pos > 0
			then
				bef_chars := ltrim(substr(x_str, startp,
					break_pos - startp + 1));
				if nvl(bef_chars, '@@xyzzy') = '@@xyzzy'
				then
					break_pos := 0;
				end if;
			end if;
			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;
			if break_pos + lf_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
				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;
	lv_column_name := ' ';
	open commt_cursor;
	loop
		fetch commt_cursor into
			lv_owner,
			lv_table_name,
			lv_table_type,
			lv_comments;
		exit when commt_cursor%NOTFOUND;
		a_lin := wri(a_lin, 'COMMENT ON TABLE ', 0);
		a_lin := wri(a_lin, lv_owner || '.' || lv_table_name, 1);
		a_lin := wri(a_lin, ' IS ', 0);
		a_lin := brkline(a_lin, &q || lv_comments || &q, 0);
		a_lin := wri(a_lin, ';', 1);
	end loop;
	close commt_cursor;
	open commc_cursor;
	loop
		fetch commc_cursor into
			lv_owner,
			lv_table_name,
			lv_column_name,
			lv_comments;
		exit when commc_cursor%NOTFOUND;
		a_lin := wri(a_lin, 'COMMENT ON COLUMN ', 0);
		a_lin := wri(a_lin, lv_owner || '.' || lv_table_name ||
			'.' || lv_column_name, 1);
		a_lin := wri(a_lin, ' IS ', 0);
		a_lin := brkline(a_lin, &q || lv_comments || &q, 0);
		a_lin := wri(a_lin, ';', 1);
	end loop;
	close commc_cursor;
	commit;
exception
	when others then
		rollback;
		raise_application_error(-20000,
			'Unexpected error on ' || lv_owner ||
			'.' || lv_table_name || ': ' ||
			to_char(SQLCODE) || ' - Aborting...');
end;
/

set termout off
set heading off
spool cr_comm.sql
select text from comm_temp order by ownr, tabnam, colnam, lineno;
spool off
drop table comm_temp;
set termout on
select 'Created cr_comm.sql...' from dual;
set termout off
exit