how to add a carriage return in select statement 1

[huchen]

Hello, I want to add a carriage return in my select statement before "commit;", see query below:
~r~ does not work.
This query will return few hundred rows and I want to run it dynamicly. If "commit;" is on same line, the update will fail. Could anybody help?
Thanks,

select distinct 'update '||c.owner||'.'|| c.table_name|| ' set '|| c.column_name
||' =scott.GET_encrypt_ssn('||c.column_name||' where '||c.column_name ||' not in (select test_ssn from scott.ssn_for_test); ~r~ commit; '
from dba_tab_columns c, dba_tables t
where c.table_name =t.table_name and c.column_name like '%SSN%'
and c.owner=o.owner
and t.num_rows>0
and c.owner in ('SCOTT','TIGER');

 

[Dagon]

There are a couple of possibilities. Either include it as part of the string i.e.

select 'x
' from dual

or append CHR(10)

e.g.

select 'x'||chr(10) from dual;

 

[huchen]

it works. thank you very much.