newline or carriage return in sql statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: newline or carriage return in sql statement

  1. #1
    Join Date
    May 2000
    Posts
    50

    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.

  2. #2
    Join Date
    May 2002
    Posts
    27
    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;

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You may need chr(13)||chr(10) for Windows - i.e. CRLF

  4. #4
    Join Date
    May 2000
    Posts
    50
    Thank you Mallika. That works. And this is a unix env.

  5. #5
    Join Date
    Apr 2010
    Posts
    1

    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
  •  


Click Here to Expand Forum to Full Width