When using dbms_output to put a clob with a carriage return it is inserting an additional one, which creates additional line breaks that can be seen when running it in SQL*Plus and using spool to capture it. The database being used is Oracle 11g. For example, running this:
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF SET
VERIFY OFF SET
TRIMOUT ON SET
TRIMSPOOL ON
SET SERVEROUTPUT ON
SPOOL ./TestResult.txt
-- Spooling direct select for comparing results
SELECT 'line 1'||CHR(13)||CHR(10)||'line 2' FROM DUAL;
declare
l_clob clob;
begin
l_clob := 'line 1'||chr(13)||chr(10)||'line 2';
dbms_output.put_line(l_clob);
end;
/
SPOOL OFF
Produces:
line 1
line 2
line 1
line 2
With the underlying line breaks going like:
[CR][LF]
line 1[CR][LF]
line 2[CR][LF]
[CR][LF]
line 1[CR]
[CR][LF]
line 2[CR][LF]
[CR][LF]
As additional note, the code above is a simplified example to illustrate my issue. In reality, the CRLF line jumps came from a clob column that I cannot modify, but I need to process before creating some client side log files with spool, as I cannot use utl_file or similar functions for creating them. Also this should be handled inside PL/SQL or SQL*Plus if possible, so I am not looking for solutions in the line of eliminating the extra CRs externally with a text editor, sh/bat scripts or similar methods.
I have tried to find why it is inserted or how to get rid of it to no avail, so any help will be greatly appreciated.