|
--
-- FIND_NON_PRINTERS.SQL
--
-- Peter Robson, 11.12.2002
--
-- Run script with name of table to interrogate as arguement:
--
-- SQL>find_non_printers table_name
--
-- Looks in all varchar2 attributes greater than 10 bytes wide.
-- Output reult in ascii_errors.txt
--
set feedback off
set verify off
set pagesize 0
set echo off
set sqlprompt ""
set termout off
--
-- First - looking for ascii(10)
--
spool a.sql
select
'select count(*) Rows_With_Ascci_10,'|| chr(10)||
''''||column_name||''''||' Col_Name from '|| chr(10)||
'&1 where '|| chr(10)||
'instr('||column_name||',chr(10)) > 0;' ||chr(10)||
' '
from user_tab_columns
where table_name = upper('&1')
and data_type='VARCHAR2'
and data_length > 10
;
spool off
--
--
spool b.sql
select
'select count(*) Rows_With_Ascci_13,'|| chr(10)||
''''||column_name||''''||' Col_Name from '|| chr(10)||
'&1 where '|| chr(13)||
'instr('||column_name||',chr(13)) > 0;' ||chr(10)||
' '
from user_tab_columns
where table_name = upper('&1')
and data_type='VARCHAR2'
and data_length > 10
;
spool off
set space 6
set termout on
--
spool ascii_errors.txt
select ' ' from dual;
select
' Rows with Ascii_10','Corrupt Column' from dual;
select
'-------------------','--------------' from dual;
@a
select ' ' from dual;
select
' Rows with Ascii_13','Corrupt Column' from dual;
select
'-------------------','--------------' from dual;
@b
select ' ' from dual;
spool off
set space 1
set SQLPROMPT 'SQL>'
set feedback on
set verify on
set pagesize 9999
--
|
|
