OraFAQ Forum: Server Utilities » Sql*plus and CHR(10)

Home » RDBMS Server » Server Utilities » Sql*plus and CHR(10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql*plus and CHR(10) [message #72441]

Tue, 17 June 2003 17:10

Kyle Channing
Messages: 2
Registered: June 2003

Junior Member

I have a 2 column table LU_ASMT_COMMUNITY with fields CODE and DESCRIPTION. If I run the following script I get these included results ..

select code || '*' || description || '*' || sysdate from lu_asmt_community

14O*Residual Ward 14 - Sub Area 14o*17-JUN-03
14P*Residual Ward 14 - Sub Area 14p*17-JUN-03
14Q*Residual Ward 14 - Sub Area 14q*17-JUN-03
14S*Residual Ward 14 - Sub Area 14s*17-JUN-03
14T*Residual Ward 14 - Sub Area 14t*17-JUN-03
UKN*TYPE UNKNOWN*17-JUN-03

But if I add a CHR(10) to the middle of the script, as follows, I get different results ...

select code || '*' || CHR(10) || description || '*' || sysdate from lu_asmt_community

14O*
Residual Ward 14 - Sub Area 14o*17-JUN-03

14P*
Residual Ward 14 - Sub Area 14p*17-JUN-03

14Q*
Residual Ward 14 - Sub Area 14q*17-JUN-03

14S*
Residual Ward 14 - Sub Area 14s*17-JUN-03

14T*
Residual Ward 14 - Sub Area 14t*17-JUN-03

UKN*
TYPE UNKNOWN*17-JUN-03

The data returned is correct but Sql*plus adds a blank line after each record returned. If I use Toad, another Oracle tool, I do not get this result. Does anyone know why this is possibly happening?

Report message to a moderator

Re: Sql*plus and CHR(10) [message #72444 is a reply to message #72441]

Wed, 18 June 2003 03:03

waqas
Messages: 3
Registered: May 2002

Junior Member

Common sense says that the character against that char(10) is a blank line i.e why that command behaves like that...

Nothing to worry abat!

Report message to a moderator

Re: Sql*plus and CHR(10) [message #72449 is a reply to message #72441]

Wed, 18 June 2003 07:30

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Kyle,
chr(10) adds a new line.
Chr(10) and other formatting are meant to be used with sql*plus ( which is a TBI- text based interface ).
TOAD and other tools use a different font systems.
so, you may get different resullts or no results at all.
if you are still curious, try these
I have no toad with me.
I tried on sql*navigator, for
select ename||'*'||chr(10)||sal from emp;
the result displayed was 
ename*some_square_symbolsal
i copied the same text(form sql*navigator) and pasted here, in this html based forumn ( true and native fonts ) and u get the expected results
SMITH*
800
ALLEN*
1600
WARD*
1250
JONES*
2975
MARTIN*
1250
BLAKE*
2850
CLARK*
2450
SCOTT*
3000
KING*
5000
TURNER*
1500
ADAMS*
1100
JAMES*
950
FORD*
3000
MILLER*
1300

Report message to a moderator

Re: Sql*plus and CHR(10) [message #72550 is a reply to message #72444]

Tue, 15 July 2003 17:15

Kyle Channing
Messages: 2
Registered: June 2003

Junior Member

The problem was that there is a setting in SQL*Plus which, under certain circumstance, will add blank lines between the output records. To turn this off you have to set the follwoingproperty at the top of your script ...

set recsep off

This was not common sense problem. The other problem is that SQL*Plus will show a list of enviroment variables which can be set. Unfortunately this env. variable does not appear in the list.

Report message to a moderator

Previous Topic:	Import Tables from SYSTEM Tablespace (urgent)
Next Topic:	Synchronization with Oracle Databases - Help Urgent!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 24 21:07:07 CST 2024