The attached code will list the owner, table, columns containing trailing spaces. This is good for data cleanup (used a lot in data warehousing). You can switch it easily to see the ones with leading spaces.

set linesize 300
drop table t_trailing;

create table t_trailing
(owner varchar2(30)
,table_name varchar2(30)
,column_name varchar2(30)
)
storage(initial 64K next 64K pctincrease 0)
pctfree 0 pctused 80;

spool c:\clients\pg\trailing_char_check.sql

— create the insert statements

select ‘insert into t_trailing’||chr(10)||
‘select ”’||owner||”’ owner,”’||table_name||”’ table_name,”’||
column_name||”’ column_name from ‘||owner||’.’||table_name||chr(10)||
‘where substr(‘||column_name||’,-1,1) = ” ” and rownum =1;’
from dba_tab_columns
where owner not in (‘SYS’,’SYSTEM’)
and data_type = ‘CHAR’
and data_length > 1;
spool off

— run the insert statements

@c:\clients\pg\trailing_char_check.sql

— see the results

break on owner skip 1 on table_name
spool c:\clients\pg\columns_with_trailing_blanks.lst
select owner,table_name,column_Name from t_trailing order by owner,table_name;
spool off
drop table t_trailing;

How to find trailing spaces in Oracle scripts

Leave a Reply

Your email address will not be published.

forty five − thirty six =