| spool result [message #194077] |
Wed, 20 September 2006 13:38  |
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 #194568 is a reply to message #194560] |
Fri, 22 September 2006 12:59  |
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 #198332 is a reply to message #198095] |
Mon, 16 October 2006 11:49 |
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 #198560 is a reply to message #198518] |
Tue, 17 October 2006 12:08 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
