The 11g docs (Oracle XML DB Developer's Guide 11g Release 2 11.2) states:
"PL/SQL and APIs – Using PL/SQL or programmatic APIs, you can retrieve XML data into VARCHAR, CLOB, or XMLType data types. As for SQL clients, you can control the encoding of the retrieved data by setting NLS_LANG."
When I select data using SQLPlus I see this behaviour exactly, but when I select data using PL/SQL or code the setting of NLS_LANG does not influence the encoding of the retrieved XML.
(This seems like a fundmental but I have searched everywhere and cannot find answer....).
Here's a very cut-down example to illustrate the problem:
First here's the environment:
$ export NLS_LANG='English'
$ echo $NLS_LANG
English
$ sqlplus jim/pw
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 17 10:18:22 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL >select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY #
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.2.0
SQL> select * from nls_session_parameters
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY £
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR HH24:MI:SS
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY ¿
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Now here's the test case:
1) Create a test table and insert a row:
SQL> CREATE TABLE xml_test OF XMLType;
SQL> INSERT INTO xml_test
VALUES
(XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<ActionRequest InstanceID="32" RequestID="32" TypeID="10" Status="0" Reference="WSREF10000200">
</ActionRequest>'));
1 row created.
2) Now let's select the data to demonstrate the problem:
2a) First leave NLS_LANG as English.
Encoding is "ISO-8859-1" in both cases as expected - all OK so far.
SQL> SELECT * from xml_test;
<?xml version="1.0" encoding="ISO-8859-1"?>
<ActionRequest InstanceID="32" RequestID="32" TypeID="10" Status="0" Reference="WSREF10000200"/>
SQL> DECLARE
v_foo XMLType;
BEGIN
SELECT * INTO v_foo from xml_test;
dbms_output.put_line(v_foo.getclobval());
END;
/
<?xml version="1.0" encoding="ISO-8859-1"?>
<ActionRequest InstanceID="32" RequestID="32" TypeID="10" Status="0" Reference="WSREF10000200"/>
2b) Now let's change the NLS_LANG to Turkish.
The encoding for the staight SQL SELECT changes to Turkish encoding "ISO-8859-9", but the PL/SQL remains as "ISO-8859-1".
$ export NLS_LANG='Turkish'
$ echo $NLS_LANG
Turkish
SQL> SELECT * from xml_test;
<?xml version="1.0" encoding="ISO-8859-9"?>
<ActionRequest InstanceID="32" RequestID="32" TypeID="10" Status="0" Reference="WSREF10000200"/>
SQL> DECLARE
v_foo XMLType;
BEGIN
SELECT * INTO v_foo from xml_test;
dbms_output.put_line(v_foo.getclobval());
END;
/
<?xml version="1.0" encoding="ISO-8859-1"?>
<ActionRequest InstanceID="32" RequestID="32" TypeID="10" Status="0" Reference="WSREF10000200"/>
It does not matter what I set the NLS_LANG to - the direct SELECT statement always honours it (i.e. encoding of the retrieved XML changes accordingly), but the encoding of the PL/SQL selected XML never changes from ISO-8859-1.
My reading of the documentation indicates that it should chaange in line with NLS_LANG - my question is how do I get it to behave as documented.