|
--
-- FIND_NON_PRINTERS_PROMPT.SQL
--
-- Peter Robson, 11.12.2002
--
-- Run script without any arguements:
--
-- SQL>find_non_printers_prompt
--
-- Looks in all varchar2 attributes greater than 10 bytes wide.
-- Output result is in ascii_errors.txt
--
set feedback off
set verify off
set pagesize 0
set echo off
set sqlprompt ""
prompt HERE! &1 = table &2 ascii no. &3 max length to scan
set termout off
--
-- First - looking for ascii(10)
--
spool a.sql
select
'select count(*) Rows_With_Ascci_&2,' ||chr(10)||
''''||column_name||''''||' Col_Name from '||chr(10)||
'&1 where ' ||chr(10)||
'instr('||column_name||',chr(&2)) > 0;' ||chr(10)||
' '
from user_tab_columns
where table_name = upper('&1')
and data_type='VARCHAR2'
and data_length > &3
;
spool off
--
set space 6
set termout on
--
spool ascii_errors.txt
select ' ' from dual;
select
' Rows with Ascii_&2','Corrupt Column' from dual;
select
'-------------------','--------------' from dual;
@a
select ' ' from dual;
spool off
set space 1
set SQLPROMPT 'SQL>'
set feedback on
set verify on
set pagesize 9999
--
|
|
