-
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 || ''');'
from dba_2pc_pending
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.
-Prasad.
-
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.
-
SQL Formatting
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), ';',
a.BCCOUNT_STATUS, ';',
substr(decryptstr(b.first_name,'&enc_key'),1,20), ';',
substr(decryptstr(b.last_name,'&enc_key'), 1,20), ';',
a.SYS_CREATION_DATE
from registration_info r, accounts a, addresses b
where r.cy_account_id=a.cy_account_id
and r.fi_id=a.fi_id
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';
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|