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.

Brian Tkatch wrote:
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...

On Nov 21, 12:48 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
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

On Wed, 21 Nov 2007 19:48:11 +0100, Frank van Bortel
<frank.van.bortel (AT) gmail (DOT) com> wrote:

My linesize is set to 156, which does not wordwrap normally.

B.

On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarrell (AT) cox (DOT) net"
<fitzjarrell (AT) cox (DOT) net> wrote:


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.

On 21 nov, 20:53, Brian Tkatch <N/A> wrote:
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.

On Nov 21, 7:40 pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:
SET RECSEP OFF
The default setting is WRAP.
<http://download.oracle.com/docs/cd/B...er.102/b14357/
ch12040.htm#SQPUG097>

Or use a client other than SQL*Plus.

On Thu, 22 Nov 2007 04:32:22 -0800 (PST), Frank van Bortel
<frank.van.bortel (AT) gmail (DOT) com> wrote:

True. But i don't see what that has to do with anything. I did not
mean to sound like i was complaining how i want something to work. I
am asking why it works the way that it does.

There is, if i want it to look pretty.

Actually, i despise redundancy, and enjoy an elegant solution. The
extra CR just plain bothers me, that is, if i know there is a way to
avoid it.

I never even thought of doing that. Thanx!

Heh, i used CHAR(9).

Perhaps i should just post the query i used. I'll never learn if i'm
too embarrassed to be shown my own mistakes. The TABLE names all start
with a four character prefix, a letter or number, two numbers, and an
underscore. The column names are prefixed similarly, minus the very
first character. It also has a SYNONYM with the number pointing to it,
for compliance with the numbered system.

WITH
Info
AS
(
SELECT
Owner,
Table_Name,
MAX(Column_Id) Total_Columns,
MAX(FLOOR(LENGTH(Column_Name) / 8)) Tabs
FROM
All_Tab_Columns
GROUP BY
Owner,
Table_Name
)
SELECT
SUBSTR
(
CASE
WHEN Columns.Column_Id = 1 THEN
'CREATE OR REPLACE VIEW ' ||
INITCAP(SUBSTR(Columns.Table_Name, 9))
CHR(10) || 'FROM'
ELSE ','
END,
1,
150
) Statement
FROM
All_Tab_Columns Columns,
Info
WHERE
Columns.Owner = 'xxx'
AND Columns.Table_Name LIKE 'xxxxx%'
AND Columns.Owner = Info.Owner
AND Columns.Table_Name = Info.Table_Name
ORDER BY
Columns.Table_Name,
Columns.Column_Id;

B.

Brian Tkatch wrote:
It is quite often an indication of (bad) SQL Server habits
being ported to Oracle. Oracle does things differently; it's
better to start with that in mind. Creating objects on the fly
is something that should not be permitted in any environment,
even SQL Server.
For SQL Server, temporary tables may be an exception.

I just wanted to point that out... There's one exception, I can
think of - when delivering an update (or install) script for
maintenance purposes.

Well - who's going to see the code? It was my understanding the code
being generated was to be executed, using dynamic SQL?
[snip!]
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

On Mon, 26 Nov 2007 19:41:26 +0100, Frank van Bortel
<frank.van.bortel (AT) gmail (DOT) com> wrote:

Although i have worked with SQL Server, i don't think i picked up any
bad habits from it. Most of my DB training is from DB2 and Oracle.

Yep, just for me. But still, if it can look nice, it might as well. I
consider it good training at the very least.

B.