newline or carriage return in sql statement
Is it possible to go to newline or put carriage return from a sql statement. I am not using any pl/sql block, so don't suggest using dbms_output.putline.
This is what I am doing:
select 'Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''|| LOCAL_TRAN_ID || ''');'
order by fail_time;
That statement gives an output of all the transactions that I have to purge. But each statement has to have an explicit commit. So how can I achieve it. Putting commit in the same line does not work. I have tried that.
execute the below statement to get the desired result.
select 'Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('''|| LOCAL_TRAN_ID || ''');'||chr(10)||'commit;'
from dba_2pc_pending order by fail_time;
You may need chr(13)||chr(10) for Windows - i.e. CRLF
Thank you Mallika. That works. And this is a unix env.
I have a script that I need to format the output... Instead of semicolon as a delimiter, use a comma. At the end of every line, insert a new line and carriage return. I need a column header andf footer and page number. Here is the script.... Thanks in advance..
Select substr(decryptstr(cc_number,'&enc_key'),1,16), ';',
substr(decryptstr(b.last_name,'&enc_key'), 1,20), ';',
from registration_info r, accounts a, addresses b
and r.CY_USER_ID = a.CY_USER_ID
and r.CY_USER_ID = b.CY_USER_ID
and r.FI_ID = b.FI_ID
and a.ADDRESS_ID = b.ADDRESS_ID
and r.fi_id = '12345.test';
Click Here to Expand Forum to Full Width