Tom,

This seems like a simple question, but I haven't figured it out yet. I am trying to use sql creating sql to build update statements for me, as we just got a new business requirement that all varchar2 columns should no longer have a NULL and should have a single space. While a not null constraint will solve the problem for future inserts, the existing data needs to be changed. What do you think the best approach would be? I originally coded

set echo off
set head off
set pagesize 0
set linesize 200
spool c:log.log
select 'update '||table_name||' set '||column_name||' = '' '' where '||column_name||' is null;'
from dba_tab_cols
where table_name = 'EMP' and
owner = 'SCOTT' and
data_type = 'VARCHAR2'
/
select 'commit;' from dual;
spool off

but realized that with large tables and maybe 80+ varchar2 columns in them, that may run out of undo. So I thought putting a commit after the update of each column would be wise, but I am having trouble making it be two distinct lines....unless you do not think the "non-printable" character at the end of the line will be a problem.

set echo off
set head off
set pagesize 0
set linesize 200
spool c:log.log
select 'update '||table_name||' set '||column_name||' = '' '' where '||column_name||' is null;'||chr(13)||chr(10)||'commit;'
from dba_tab_cols
where table_name = 'EMP' and
owner = 'SCOTT' and
data_type = 'VARCHAR2'
/
spool off


Should I worry about the linefeed/carriage return showing up in my spool file, or is there a way to strip that out in the select? Is there another method to get a line of update and a line of commit??

I was in the process of searching your site for "display results two lines" and "break results" (ooo I should try break output) when a question opened up. Currently, I'm trying something like this, but can't figure out how to make it return more than one row (emp has several varchar2 columns), and once that happens, avoid the duplicates from the union.

select CASE mod(rownum, 2)
when 1 then 'update '||table_name||' set '||column_name||' = '' '' where '||column_name||' is null;'
when 0 then 'commit;'
end
from (select rownum, table_name, column_name
from (select rownum, table_name, column_name
from dba_tab_cols
where table_name = 'EMP' and
owner = 'SCOTT' and
data_type = 'VARCHAR2')
UNION
(select rownum, table_name, column_name
from dba_tab_cols
where table_name = 'EMP' and
owner = 'SCOTT' and
data_type = 'VARCHAR2'))


I would appreciate any help you have to offer!

Thanks,
Dennis