rem $DBA/chktabl2.sql
rem
rem Checks a specific table's CHAR and VARCHAR2 columns for abnormal characters.
rem
rem Prompts for the table owner and the table name to be checked.
rem
rem Requires Oracle V7.2.2.3 (since dynamic SQL in PL/SQL version 2.2.2.3 is
rem used)
rem
rem Not as efficient as chktable.sql, since this script fetches all rows from
rem the table for EACH char and varchar2 column in the table.  Used only when
rem there are more than 10 char and/or varchar2 columns in a table, thereby
rem prohibiting the use of the chktable.sql script (unless rewritten to handle
rem the additional columns).
rem
rem Created 06/24/97 by Brian Lomasky
rem
set echo off
set feedback off
set pagesize 0
set verify off
set serveroutput on size 100000
set termout on
declare
	cursor col_cursor is select
		owner,
		table_name,
		column_name,
		data_type,
		data_length
		from sys.dba_tab_columns
		where owner = upper('&&table_owner') and
		table_name = upper('&&table_name')
		order by column_id;

	lv_owner		sys.dba_tab_columns.owner%TYPE;
	lv_table_name		sys.dba_tab_columns.table_name%TYPE;
	lv_column_name		sys.dba_tab_columns.column_name%TYPE;
	lv_data_type		sys.dba_tab_columns.data_type%TYPE;
	lv_data_length		sys.dba_tab_columns.data_length%TYPE;
	abnormal		number;
	n			number;
	i			number;
	a			number;
	rows_processed		number;
	rowno			number;
	cursor1			integer;
	out_char		char(2000);
	out_varchar2		varchar2(2000);
begin
	open col_cursor;
	loop
		fetch col_cursor into
			lv_owner,
			lv_table_name,
			lv_column_name,
			lv_data_type,
			lv_data_length;
		exit when col_cursor%notfound;
		if lv_data_type = 'CHAR' or lv_data_type = 'VARCHAR2' then
			dbms_output.put_line('Processing column name: ' ||
				lv_column_name || '...');
			cursor1 := dbms_sql.open_cursor;
			dbms_sql.parse(cursor1, 'select ' || lv_column_name ||
				' from ' || lv_owner || '.' || lv_table_name,
				dbms_sql.native);
			if lv_data_type = 'CHAR' then
				dbms_sql.define_column(cursor1,1,out_char,
					lv_data_length);
			else
				dbms_sql.define_column(cursor1,1,out_varchar2,
					lv_data_length);
			end if;
			rows_processed := dbms_sql.execute(cursor1);
			abnormal := 0;
			rowno := 0;
			loop
				if dbms_sql.fetch_rows(cursor1) = 0 then
					exit;
				end if;
				rowno := rowno + 1;
				if lv_data_type = 'CHAR' then
					dbms_sql.column_value(cursor1, 1,
						out_char);
					out_varchar2 := substr(out_char, 1,
						lv_data_length);
				else
					dbms_sql.column_value(cursor1, 1,
						out_varchar2);
				end if;
				/* ---------- Check for quote ---------- */
				n := instr(out_varchar2, chr(34));
				if n <> 0 then
					dbms_output.put_line('.  Row ' ||
						to_char(rowno) ||
						' has an embedded quote' ||
						' at position ' || to_char(n));
					abnormal := 1;
					exit;
				end if;
				/* ---------- Check for apostrophe ---------- */
				n := instr(out_varchar2, chr(39));
				if n <> 0 then
					dbms_output.put_line('.  Row ' ||
						to_char(rowno) ||
						' has an embedded apostrophe' ||
						' at position ' || to_char(n));
					abnormal := 1;
					exit;
				end if;
				/* ---------- Check for any ---------- */
				/* ---------- control characters ---------- */
				i := 0;
				while i < length(out_varchar2)
				loop
					i := i + 1;
					a := ascii(substr(out_varchar2,i,1));
					if a < 32 then
						dbms_output.put_line(
							'.  Row ' ||
							to_char(rowno) ||
							' has an ASCII' ||
							' character of <' ||
							to_char(a) ||
							'> at position ' ||
							to_char(n));
						abnormal := 1;
						exit;
					end if;
				end loop;
				if abnormal <> 0 then
					exit;
				end if;
			end loop;
			if abnormal = 0 then
				dbms_output.put_line(
					'.  (No abnormal characters found' ||
					' in ' || to_char(rowno) ||
					' rows...)');
			end if;
			dbms_sql.close_cursor(cursor1);
		else
			dbms_output.put_line('Skipping column ' ||
				lv_column_name || ' - not CHAR or VARCHAR2...');
		end if;
	end loop;
	close col_cursor;
	commit;
exception
        when others then
                rollback;
		if dbms_sql.is_open(cursor1) then
			dbms_sql.close_cursor(cursor1);
		end if;
                raise_application_error(-20000,
                        'Unexpected error on column ' || lv_column_name ||
			chr(10) || SQLERRM || chr(10) || 'Aborting...');
end;
/
exit