 | |
Does CHR(10) add an extra newline?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Does CHR(10) add an extra newline? -
11-27-2007
, 11:09 AM
|
On 21 nov, 20:53, Brian Tkatch <N/A> wrote: On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr... (AT) cox (DOT) net" fitzjarr... (AT) cox (DOT) net> wrote: On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) 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 Thank you. I came up with it originally in a CASE statement, to create CREATE VIEW statements. I worked around it by opening Word, and doing a replace on ",^p^p". Though, i am quite curious why this is happening. And thanx for the example in PL/SQL. B. You normally would not create views "on the fly"... Apart from that - there's no need for carriage returns in DDL, but if you really want them, just embed the carriage return in the statement: SQL> select 'hello 2 world' from dual; 'HELLOWORLD' ------------ hello world And yes- there's an extra space before Hello on line 2. |
SQL>select 'hello
2 world' from (SELECT * FROM Dual CONNECT BY Level < 3);
'HELLOWORLD'
------------
hello
world
hello
world
SQL>
B.
#12
| |||
| |||
|
|
On Thu, 22 Nov 2007 04:32:22 -0800 (PST), Frank van Bortel frank.van.bortel (AT) gmail (DOT) com> wrote: On 21 nov, 20:53, Brian Tkatch <N/A> wrote: On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr... (AT) cox (DOT) net" fitzjarr... (AT) cox (DOT) net> wrote: On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) 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 Thank you. I came up with it originally in a CASE statement, to create CREATE VIEW statements. I worked around it by opening Word, and doing a replace on ",^p^p". Though, i am quite curious why this is happening. And thanx for the example in PL/SQL. B. You normally would not create views "on the fly"... Apart from that - there's no need for carriage returns in DDL, but if you really want them, just embed the carriage return in the statement: SQL> select 'hello 2 world' from dual; 'HELLOWORLD' ------------ hello world And yes- there's an extra space before Hello on line 2. This does not help, there is still an extra CR after the line. SQL>select 'hello 2 world' from (SELECT * FROM Dual CONNECT BY Level < 3); 'HELLOWORLD' ------------ hello world hello world SQL B. |
If so consider using it.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#13
| |||
| |||
|
|
Brian Tkatch wrote: On Thu, 22 Nov 2007 04:32:22 -0800 (PST), Frank van Bortel frank.van.bortel (AT) gmail (DOT) com> wrote: On 21 nov, 20:53, Brian Tkatch <N/A> wrote: On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr... (AT) cox (DOT) net" fitzjarr... (AT) cox (DOT) net> wrote: On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) 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 Thank you. I came up with it originally in a CASE statement, to create CREATE VIEW statements. I worked around it by opening Word, and doing a replace on ",^p^p". Though, i am quite curious why this is happening. And thanx for the example in PL/SQL. B. You normally would not create views "on the fly"... Apart from that - there's no need for carriage returns in DDL, but if you really want them, just embed the carriage return in the statement: SQL> select 'hello 2 world' from dual; 'HELLOWORLD' ------------ hello world And yes- there's an extra space before Hello on line 2. This does not help, there is still an extra CR after the line. SQL>select 'hello 2 world' from (SELECT * FROM Dual CONNECT BY Level < 3); 'HELLOWORLD' ------------ hello world hello world SQL B. Do you remember how do you remove a trailing character from a string? If so consider using it. |
If you have nothing useful to say, please keep it to yourself.
B.
#14
| |||
| |||
|
|
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) A B ---------- ----------------------------------------- 1 1 2 2 3 3 3 rows selected. SQL How do i add a newline in middle of a line without adding at the end as well? B. |
detects the CHR(10) as a line wrap and uses RECSEPCHAR which defaults
to a space. SETting RECSEP OFF turns this off.
SQL> SET RECSEP OFF
SQL> SELECT RowNum A, CHR(10) || RowNum B FROM (SELECT * FROM Dual
CONNECT BY LEVEL < 4);
A B
---------- -----------------------------------------
1
1
2
2
3
3
SQL> SET RECSEP WRAPPED
SQL> SELECT RowNum A, CHR(10) || RowNum B FROM (SELECT * FROM Dual
CONNECT BY LEVEL < 4);
A B
---------- -----------------------------------------
1
1
2
2
3
3
B.
#15
| |||
| |||
|
|
On Tue, 27 Nov 2007 19:45:16 -0800, DA Morgan <damorgan (AT) psoug (DOT) org wrote: Brian Tkatch wrote: On Thu, 22 Nov 2007 04:32:22 -0800 (PST), Frank van Bortel frank.van.bortel (AT) gmail (DOT) com> wrote: On 21 nov, 20:53, Brian Tkatch <N/A> wrote: On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr... (AT) cox (DOT) net" fitzjarr... (AT) cox (DOT) net> wrote: On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) 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 Thank you. I came up with it originally in a CASE statement, to create CREATE VIEW statements. I worked around it by opening Word, and doing a replace on ",^p^p". Though, i am quite curious why this is happening. And thanx for the example in PL/SQL. B. You normally would not create views "on the fly"... Apart from that - there's no need for carriage returns in DDL, but if you really want them, just embed the carriage return in the statement: SQL> select 'hello 2 world' from dual; 'HELLOWORLD' ------------ hello world And yes- there's an extra space before Hello on line 2. This does not help, there is still an extra CR after the line. SQL>select 'hello 2 world' from (SELECT * FROM Dual CONNECT BY Level < 3); 'HELLOWORLD' ------------ hello world hello world SQL B. Do you remember how do you remove a trailing character from a string? If so consider using it. Weren't you going to killfile me? If you have nothing useful to say, please keep it to yourself. B. |
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#16
| |||
| |||
|
|
OK, figured it out. RECSEP by default is set to WRAPPED, so SQL*PLUS detects the CHR(10) as a line wrap and uses RECSEPCHAR which defaults to a space. SETting RECSEP OFF turns this off. |
thread 5 days ago
http://groups.google.de/group/comp.d...73ebbe5a82baee
Best regards
Maxim
#17
| |||
| |||
|
|
Brian Tkatch wrote: On Tue, 27 Nov 2007 19:45:16 -0800, DA Morgan <damorgan (AT) psoug (DOT) org wrote: Brian Tkatch wrote: On Thu, 22 Nov 2007 04:32:22 -0800 (PST), Frank van Bortel frank.van.bortel (AT) gmail (DOT) com> wrote: On 21 nov, 20:53, Brian Tkatch <N/A> wrote: On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr... (AT) cox (DOT) net" fitzjarr... (AT) cox (DOT) net> wrote: On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) 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 Thank you. I came up with it originally in a CASE statement, to create CREATE VIEW statements. I worked around it by opening Word, and doing a replace on ",^p^p". Though, i am quite curious why this is happening. And thanx for the example in PL/SQL. B. You normally would not create views "on the fly"... Apart from that - there's no need for carriage returns in DDL, but if you really want them, just embed the carriage return in the statement: SQL> select 'hello 2 world' from dual; 'HELLOWORLD' ------------ hello world And yes- there's an extra space before Hello on line 2. This does not help, there is still an extra CR after the line. SQL>select 'hello 2 world' from (SELECT * FROM Dual CONNECT BY Level < 3); 'HELLOWORLD' ------------ hello world hello world SQL B. Do you remember how do you remove a trailing character from a string? If so consider using it. Weren't you going to killfile me? If you have nothing useful to say, please keep it to yourself. B. Killed the thread not you. But if you'd like I'd be happy to do so. |
and aggrivating. But to others, your signal to noise ration is still
good.
B.
#18
| |||
| |||
|
|
Brian Tkatch schrieb: OK, figured it out. RECSEP by default is set to WRAPPED, so SQL*PLUS detects the CHR(10) as a line wrap and uses RECSEPCHAR which defaults to a space. SETting RECSEP OFF turns this off. My impression was, William Robertson was talking about it to you in this thread 5 days ago http://groups.google.de/group/comp.d...73ebbe5a82baee Best regards Maxim |
I misunderstood what he meant when he first posted, thinking it
similar to the first reply i got to the post where the replier
obviously did not try it himself. William didn't explain why it was
thinking it was wrapping, so i ignored him too as a useless post.
Thank you for pointing it out.
B.
#19
| |||
| |||
|
|
On Wed, 28 Nov 2007 13:32:26 -0800, DA Morgan <damorgan (AT) psoug (DOT) org wrote: Brian Tkatch wrote: On Tue, 27 Nov 2007 19:45:16 -0800, DA Morgan <damorgan (AT) psoug (DOT) org wrote: Brian Tkatch wrote: On Thu, 22 Nov 2007 04:32:22 -0800 (PST), Frank van Bortel frank.van.bortel (AT) gmail (DOT) com> wrote: On 21 nov, 20:53, Brian Tkatch <N/A> wrote: On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr... (AT) cox (DOT) net" fitzjarr... (AT) cox (DOT) net> wrote: On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) 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 Thank you. I came up with it originally in a CASE statement, to create CREATE VIEW statements. I worked around it by opening Word, and doing a replace on ",^p^p". Though, i am quite curious why this is happening. And thanx for the example in PL/SQL. B. You normally would not create views "on the fly"... Apart from that - there's no need for carriage returns in DDL, but if you really want them, just embed the carriage return in the statement: SQL> select 'hello 2 world' from dual; 'HELLOWORLD' ------------ hello world And yes- there's an extra space before Hello on line 2. This does not help, there is still an extra CR after the line. SQL>select 'hello 2 world' from (SELECT * FROM Dual CONNECT BY Level < 3); 'HELLOWORLD' ------------ hello world hello world SQL B. Do you remember how do you remove a trailing character from a string? If so consider using it. Weren't you going to killfile me? If you have nothing useful to say, please keep it to yourself. B. Killed the thread not you. But if you'd like I'd be happy to do so. I'd very much like you too. Most of your responses to me are useless and aggrivating. But to others, your signal to noise ration is still good. B. |
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.