Home » RDBMS Server » Server Administration » spool result
spool result [message #194077] Wed, 20 September 2006 13:38 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

hi:

I´m getting a problem with the spool. when i'm querying a table with my client I get the follow

spool d:\prueba.txt;
SELECT FIELD1 FROM TABLE1
WHERE FIELD1 LIKE 'MU%';
spool off;

IN THE CLIENT
-------------
MUÑOZ

IN THE FILE
------------
MU¥OZ

How can i set my spool, so that my results are the same in both cases??

What do you believe that the problem is?

Thanks Alex.
Re: spool result [message #194451 is a reply to message #194077] Fri, 22 September 2006 05:12 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
different nls_lang
Re: spool result [message #194560 is a reply to message #194451] Fri, 22 September 2006 11:39 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

do you know anyone??

thanks
Re: spool result [message #194568 is a reply to message #194560] Fri, 22 September 2006 12:59 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
First you need to know the 2 charactersets the database was created with:
select parameter, value, banner
  from v$nls_parameters a, v$version
  where parameter like '%CHARACTERSET%'
  and banner like '%Enterprise%';

PARAMETER	VALUE	BANNER
NLS_CHARACTERSET	WE8ISO8859P1	Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
NLS_NCHAR_CHARACTERSET	AL16UTF16	Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production



So in this case - varchar2 etc column data will be stored in ISO-1 (WE8ISO8859P1), wheras Nvarchar2 etc column data will be stored in AL16UTF16.

Do a dump on the column to see what bytes are actually used to make sure it's stored correctly in the database.
select field1, vsize(field1), dump(field1, 1010) Decimal_bytes, dump(field1, 1016) Hex_Bytes from table1 where...;


From sqlplus, check what the current NLS lang is set to:

SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.WE8MSWIN1252"

Ignore the actual error message - the thing to note in this case is that the characterset is set to Microsoft codepage 1252 (Windows) WE8MSWIN1252.


Your client codepage needs to be compatible with your database characterset (not necessarily the same). Change your client nls_lang to be compatible with your database.
Re: spool result [message #198095 is a reply to message #194568] Sat, 14 October 2006 15:59 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

where can i find this information??

Change your client nls_lang to be compatible with your database??

Thanks to all
Alex
Re: spool result [message #198332 is a reply to message #198095] Mon, 16 October 2006 11:49 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
That's not an easy question to answer offhand - you need to know your database characterset and your client machine codepage capabilities. "Compatible" means generally that your client codepage(characterset) is a subset of your database characterset. If you don't have compatible codepages - you'll loose characters not supported in the target codepage (they'll often be converted to "?"). Search tahiti.oracle.com for further info.

e.g. ISO-1 (WE8ISO8859P1) is a subset of Microsoft codepage 1252 (WE8MSWIN1252) which in turn is a subset of UTF8 (AL32UTF8).

Once again - be careful of visually checking any character - it is no garantee that it's stored correctly or being displayed correctly. Use dump() to see the bytes stored in Oracle and a binary editor or "od" or whatever to see what's getting spooled.

In general - isqlplus is much better for viewing special characters than windows or DOS sqlplus because browsers have much better characterset support than windows apps.


Re: spool result [message #198369 is a reply to message #194077] Mon, 16 October 2006 20:47 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

When you are talking about subsets

ISO-1 (WE8ISO8859P1) is a subset of Microsoft codepage 1252 (WE8MSWIN1252) which in turn is a subset of UTF8 (AL32UTF8).

I understand that if the character is in the major set, this character is available for subsets and if the set can´t sopport the character then subset can´t neither??

For example if the character A is supported for UTF8; it means that it is supported for WE8MSWIN1252 and WE8ISO8859P1 too?

Thanks for your tips andrew
Alex
Re: spool result [message #198518 is a reply to message #198369] Tue, 17 October 2006 08:30 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

You are right!!

I used the characterset WE8MSWIN1252 in my control file and view my result in the SQL*Plus given by Oracle (not via MS-DOS) and the characters are shown....

I don´t understand why if i can´t write these characters in the ms-dos command line and they appears correctly...

Well thanks for tips again

Greetings
Alex
Re: spool result [message #198560 is a reply to message #198518] Tue, 17 October 2006 12:08 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
For my particular US setting PC, my Windows codepage is 1252 and for DOS it is 437. Here is a DOS command test using text based sqlplus.

--===============================================================
Euro is CHR(14844588) (U+20AC)
--=============================================================
1.) Set NLS_LANG in registry for current Oracle Home to something having a Euro
 e.g. AMERICAN_AMERICA.WE8MSWIN1252

-- show NLS_LANG setting
SQL> @%NLS_LANG%
SP2-0310: unable to open file "AMERICAN_AMERICA.WE8MSWIN1252"

--=============================================================
-- Default DOS codepage 437 (Euro sign display test fails)
--=============================================================
C:\>chcp
Active code page: 437

C:\>sqlplus utf8test/utf8test@utf8db

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Oct 7 14:27:37 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

SQL> select CHR(14844588) from dual;

CHR 
--- 
Ç   <<=== Incorrect display (default codepage 437)

--=============================================================
-- Windows codepage 1252 (Euro sign display test works)
--=============================================================
2.) C:\>chcp 1252
Active code page: 1252

3.) Set font in DOS window to Lucida Console (it contains Euro)

4.) C:\>sqlplus utf8test/utf8test@utf8db

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Oct 7 14:28:21 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

2.) SQL> select CHR(14844588) from dual;

CHR
---
¤   <<=== Correct display (codepage 1252)



You can see which characters are in each of the common charactersets here
http://h20000.www2.hp.com/bc/docs/support/SupportManual/bpl13206/bpl13206.pdf

UFT8 is a massive characterset, so I don't know of too many charts of all the characters - but you can start here.
http://www.i18nguy.com/unicode/codepages.html

Most of the common/modern/ISO charactersets have the 7 bit ASCII codes the same throughout, so an "A" in UTF8, or ISO-1 or MS codepage 1252 are all the same, but with say a Euro symbol, ISO-1 doesn't have the character, MS 1252 has it in one place and UTF8 has it represented as 3 bytes. UTF8 is just one particular encoding scheme for Unicode characters.

[Updated on: Tue, 17 October 2006 12:15]

Report message to a moderator

Previous Topic: CLUSTER ISSUE
Next Topic: Remove rule from the streams
Goto Forum:
  


Current Time: Mon Dec 05 23:56:38 CST 2016

Total time taken to generate the page: 0.23022 seconds