Re: Does CHR(10) add an extra newline?

From: <fitzjarrell_at_cox.net>
Date: Wed, 21 Nov 2007 11:40:24 -0800 (PST)
Message-ID: <8cbf9845-b3ef-4f98-8b7f-76a4fa6888da@41g2000hsh.googlegroups.com>

On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> Brian Tkatch wrote:
> > While writing a query to do some editting i wanted to use CHR(10) to
> > have a query do some formatting. It seems that whenever it is used, it
> > adds an extra newline at the end of the line.
>
> > SQL> SELECT RowNum A, CHR(10) || RowNum B FROM (SELECT * FROM Dual
> > CONNECT BY LEVEL < 4)
>
> No - your display simply is not wide enough.
> Why don't you check out all possible settings in SQL*Plus?
> the one you want is SET LINES[ize]
>
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...

I return the same results that you posted, using 10.2.0.3.0 on AIX:

SQL> SELECT RowNum A, CHR(10) || RowNum B FROM (SELECT * FROM Dual   2 CONNECT BY LEVEL < 4);

 A B

-- -----------------------------------------

 1
   1

 2
   2

 3
   3

 no matter how long my linesize. Also the method of introducing the chr(10) into the text matters not:

SQL> variable lf varchar2
SQL> exec :lf := chr(10);

PL/SQL procedure successfully completed.

SQL> select rownum a, :lf||rownum b
  2 from dual
  3 connect by level < 4;

 A B

--
------------------------------------------------------------------------
 1
   1

 2
   2

 3
   3


SQL>

I find no explanation of this behaviour on the web, thus I am at a
loss to explain it.

You may be relegated to using PL/SQL to produce the results you want
(presuming the following would be the results you desire):

begin
   for i in (select rownum a, rownum b from dual connect by level < 4)
loop
           dbms_output.put_line(i.a || chr(10) || chr(9) || i.b);
   end loop;
end;
/

1
        1
2
        2
3
        3

PL/SQL procedure successfully completed.


David Fitzjarrell

Received on Wed Nov 21 2007 - 13:40:24 CST