Home » SQL & PL/SQL » Client Tools » different charactersets show line-break in select result (RDBMS 10gR2, 10.2.0.4, HPUX 11.31)
different charactersets show line-break in select result [message #448225] Mon, 22 March 2010 04:10 Go to next message
bensonmam
Messages: 2
Registered: March 2010
Location: Germany
Junior Member
Hello,
i have a little curiosity in result of a select statement.
One database has following characterset settings:
NLS_CHARACTERSET WE8ISO8859P15
NLS_NCHAR_CHARACTERSET AL16UTF16


and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
BUP
OPEN


Another database has following characterset settings:
NLS_CHARACTERSET UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

and the same select shows only a 2 line result:
SQL> select INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN


Both databases are version 10.2.0.4 and both are running on HPUX 11.31.
Why do we get this differents in showing the result?

Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.

regards
Ulli





Re: different charactersets show line-break in select result [message #448233 is a reply to message #448225] Mon, 22 March 2010 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ "the same select shows only a 2 line result:", I don't see 2 lines.
2/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.
3/ Use SQL*Plus and copy and paste your session, including feedback and if there are nulls, use "set null".
4/ "the following select statement results in multiple lines", use a proper line size "set linesize ..."
5/ Post "show parameter cursor_sharing" for both

Regards
Michel
Re: different charactersets show line-break in select result [message #448242 is a reply to message #448233] Mon, 22 March 2010 04:53 Go to previous messageGo to next message
bensonmam
Messages: 2
Registered: March 2010
Location: Germany
Junior Member
Hello again,
let me put some light in the dark.

"1/ "the same select shows only a 2 line result:", I don't see 2 lines."

SQL> select INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN

This is a two line result. One line shows the heading and one lines shows the result.

2/ Please read OraFAQ Forum Guide, especially "How to format your post?" section.

select INSTANCE_NAME,STATUS from v$instance;
if it is more readable i will put it in the tags.

3/ Use SQL*Plus and copy and paste your session, including feedback and if there are nulls, use "set null".
This is a copy and paste of the sqlplus session.

SQL> select INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NAME STATUS
---------------- ------------------------------------
HPSMP OPEN

4/ "the following select statement results in multiple lines", use a proper line size "set linesize ..."
I knew that i can set the linesize as workaround, but i want to understand why is the result that different.

5/ Post "show parameter cursor_sharing" for both
For both databases this parameter is set to exact. For me it makes no sense to check this parameter for a simple one-time executed select statement, but anyway.

regards
Ulli


Re: different charactersets show line-break in select result [message #448246 is a reply to message #448242] Mon, 22 March 2010 05:18 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if it is more readable i will put it in the tags.

All the SQL*Plus session must be between code tags not just the query.

Quote:
This is a copy and paste of the sqlplus session.

And why is it not formatted?

Quote:
For both databases this parameter is set to exact.

Do not tell us, SHOW us.

Quote:
I knew that i can set the linesize as workaround, but i want to understand why is the result that different.

No guarantee you have the same result unless you specify a column format. When you rely on default behaviour, you have what it has been coded as default behaviour (for the current conditions).

Regards
Michel
Previous Topic: Substitution Variables
Next Topic: execute a procedure with parameter of object type
Goto Forum:
  


Current Time: Sat Dec 10 20:36:53 CST 2016

Total time taken to generate the page: 0.12958 seconds