Home » SQL & PL/SQL » Client Tools » Redundant blank line generated (ORAQLE Sql 11, Win7)
Redundant blank line generated [message #638006] Mon, 01 June 2015 01:56 Go to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
I have this sample sql:
with p_1 as
(
          select 1 sorszam, 'X1' tipus from dual
union all select 2 sorszam, 'X2' tipus from dual
union all select 3 sorszam, 'X3' tipus from dual
)
select (
	(case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) || 
	p1.tipus 
	|| (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
	) szoveg
 from p_1 p1
 order by p1.sorszam
;

The result is:
SZOVEG
--------
[
X1

X2
X3
]

My question is: Why is generated the blank line after the first line?



[Edit MC: add code tags]

[Updated on: Mon, 01 June 2015 02:08] by Moderator

Report message to a moderator

Re: Redundant blank line generated [message #638007 is a reply to message #638006] Mon, 01 June 2015 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read How to use [code] tags and make your code easier to read.

It works for me:
SQL> with p_1 as
  2  (
  3            select 1 sorszam, 'X1' tipus from dual
  4  union all select 2 sorszam, 'X2' tipus from dual
  5  union all select 3 sorszam, 'X3' tipus from dual
  6  )
  7  select (
  8     (case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
  9     p1.tipus
 10     || (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
 11     ) szoveg
 12   from p_1 p1
 13   order by p1.sorszam
 14  ;
SZOVEG
--------
[
X1
X2
X3
]

3 rows selected.

Please post the result of:
col SZOVEG
show linesize recsep recsepchar

Re: Redundant blank line generated [message #638008 is a reply to message #638007] Mon, 01 June 2015 02:26 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
Thanks for reply! My settings are:

set pagesize 50000
set linesize 10000
CLEAR COLUMNS
SET COLSEP |
SET TRIMSPOOL ON

col SZOVEG format a50 ;

Excuse me! My ORAQLE version is: Oracle Database 10g Release 10.2.0.3.0
But I think, it doesn't affect the results.

[Updated on: Mon, 01 June 2015 02:32]

Report message to a moderator

Re: Redundant blank line generated [message #638010 is a reply to message #638008] Mon, 01 June 2015 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post the result of the commands I asked.
In addition, the version you gave is this the database or client versions? We need both and it may affect the results?
Do you use SQL*Plus or another client program?

Re: Redundant blank line generated [message #638011 is a reply to message #638010] Mon, 01 June 2015 03:20 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
I use sqlplus.exe (on Windows 7). The full version descripton is, if it is helpful:

Oracle Database 10g Release 10.2.0.3.0 - Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Re: Redundant blank line generated [message #638013 is a reply to message #638011] Mon, 01 June 2015 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again is this the SQL*Plus version or the database version?
The SQL*Plus version is displayed with its banner.
Database version is gotten with "select * from v$version".
Copy and paste SQL*Plus session, and do it formatted as explained in the link I posted.
And post the results of the commands I asked.

Re: Redundant blank line generated [message #638015 is a reply to message #638013] Mon, 01 June 2015 07:37 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
select * from v$version:

Oracle Database 10g Release 10.2.0.3.0 - Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQLPlus banner:

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 1 14:34:11 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Hoppaaa! Database is 10g , SQLPlus is 11 Shocked
Re: Redundant blank line generated [message #638016 is a reply to message #638015] Mon, 01 June 2015 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I tested with a 11.2.0.3 SQL*Plus on a 10.2.0.3 database and have the correct result.

YOU MUST POST WHAT I REQUESTED 3 TIMES.

Re: Redundant blank line generated [message #638017 is a reply to message #638016] Mon, 01 June 2015 08:04 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
Result is here:

COLUMN SZOVEG ON
FORMAT a50
linesize 10000
recsep WRAP
recsepchar " " (hex 20)

I hope you thought this.
Re: Redundant blank line generated [message #638020 is a reply to message #638017] Mon, 01 June 2015 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post result of:
show parameter cursor
Re: Redundant blank line generated [message #638021 is a reply to message #638020] Mon, 01 June 2015 09:10 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
the result of "show parameter cursor" is:

ORA-00942: table or view does not exist
Re: Redundant blank line generated [message #638022 is a reply to message #638021] Mon, 01 June 2015 09:17 Go to previous messageGo to next message
gazzag
Messages: 1043
Registered: November 2010
Location: Bristol, UK
Senior Member
SQL> SHOW USER

The "SHOW PARAMETER" command should be run as a privileged user, e.g. SYSTEM.
Re: Redundant blank line generated [message #638028 is a reply to message #638022] Mon, 01 June 2015 09:35 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
Sorry, I haven't administrator rights. Embarassed
Re: Redundant blank line generated [message #638030 is a reply to message #638006] Mon, 01 June 2015 09:38 Go to previous messageGo to next message
EdStevens
Messages: 1005
Registered: September 2013
Senior Member
vinczej wrote on Mon, 01 June 2015 01:56
I have this sample sql:
with p_1 as
(
          select 1 sorszam, 'X1' tipus from dual
union all select 2 sorszam, 'X2' tipus from dual
union all select 3 sorszam, 'X3' tipus from dual
)
select (
	(case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) || 
	p1.tipus 
	|| (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
	) szoveg
 from p_1 p1
 order by p1.sorszam
;

The result is:
SZOVEG
--------
[
X1

X2
X3
]

My question is: Why is generated the blank line after the first line?



[Edit MC: add code tags]

looks like a simple line-wrap situation. The output line is longer than the display allows.
Re: Redundant blank line generated [message #638033 is a reply to message #638030] Mon, 01 June 2015 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is what I first thought and this is why I asked about col and line size.
If what OP gave you is correct, this is not the reason and so this is a mystery... for the moment.

vinczej wrote on Mon, 01 June 2015 15:04
Result is here:
COLUMN SZOVEG ON
FORMAT a50
linesize 10000
recsep WRAP
recsepchar " " (hex 20)
...


Anyway, given the recsep format is wrapped and the blank recsepchar, I tend to think this is still the case.

Re: Redundant blank line generated [message #638034 is a reply to message #638033] Mon, 01 June 2015 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@OP,
could you execute and copy and paste exactly the following:
set lines 80
set trimout on
col  SZOVEG OFF
with p_1 as
(
          select 1 sorszam, 'X1' tipus from dual
union all select 2 sorszam, 'X2' tipus from dual
union all select 3 sorszam, 'X3' tipus from dual
)
select (
	(case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) || 
	p1.tipus 
	|| (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
	) szoveg
 from p_1 p1
 order by p1.sorszam
;

Then
set recsep off
with p_1 as
(
          select 1 sorszam, 'X1' tipus from dual
union all select 2 sorszam, 'X2' tipus from dual
union all select 3 sorszam, 'X3' tipus from dual
)
select (
	(case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) || 
	p1.tipus 
	|| (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
	) szoveg
 from p_1 p1
 order by p1.sorszam
;

You should have:
SQL> set recsep wrap
SQL> set recsepchar ' '
SQL> set lines 80
SQL> set trimout on
SQL> col  SZOVEG OFF
SQL> with p_1 as
  2  (
  3            select 1 sorszam, 'X1' tipus from dual
  4  union all select 2 sorszam, 'X2' tipus from dual
  5  union all select 3 sorszam, 'X3' tipus from dual
  6  )
  7  select (
  8     (case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
  9     p1.tipus
 10     || (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
 11     ) szoveg
 12   from p_1 p1
 13   order by p1.sorszam
 14  ;
SZOVEG
--------
[
X1

X2
X3
]


3 rows selected.

SQL> set recsep off
SQL> with p_1 as
  2  (
  3            select 1 sorszam, 'X1' tipus from dual
  4  union all select 2 sorszam, 'X2' tipus from dual
  5  union all select 3 sorszam, 'X3' tipus from dual
  6  )
  7  select (
  8     (case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
  9     p1.tipus
 10     || (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
 11     ) szoveg
 12   from p_1 p1
 13   order by p1.sorszam
 14  ;
SZOVEG
--------
[
X1
X2
X3
]


Re: Redundant blank line generated [message #638037 is a reply to message #638028] Mon, 01 June 2015 10:40 Go to previous messageGo to next message
gazzag
Messages: 1043
Registered: November 2010
Location: Bristol, UK
Senior Member
Then you must ask someone who does.
Re: Redundant blank line generated [message #638054 is a reply to message #638034] Mon, 01 June 2015 15:08 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
Thanks, Michel! Tomorrow I will try the sample (set recsep off and others) above. Practicably I have got only 1 oolunmn, the mounted text rows. Laughing )

[Updated on: Mon, 01 June 2015 15:09]

Report message to a moderator

Re: Redundant blank line generated [message #638062 is a reply to message #638054] Tue, 02 June 2015 01:43 Go to previous messageGo to next message
vinczej
Messages: 12
Registered: June 2015
Junior Member
Thanks the suggestion! "Set recsep off" is working! Mad

[Updated on: Tue, 02 June 2015 01:44]

Report message to a moderator

Re: Redundant blank line generated [message #638065 is a reply to message #638062] Tue, 02 June 2015 02:01 Go to previous message
Michel Cadot
Messages: 65969
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Previous Topic: connection problem
Next Topic: Oracle client
Goto Forum:
  


Current Time: Fri Nov 16 18:57:53 CST 2018