| different charactersets show line-break in select result [message #448225] |
Mon, 22 March 2010 04:10  |
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 #448242 is a reply to message #448233] |
Mon, 22 March 2010 04:53  |
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;
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 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
