I posted this question on Oracle Metalink back in October, but didn't
receive any feedback so I just wanted to present the issue again. ( read
metalink posting below for detail ) The goal here is to build a database
that will store all data as unicode. Ultimately, I'd rather not require a
customer to use the AL32UTF8 NLS_CHARACTERSET. All of our procedure
variables and column data types will be NVARCHAR and NCLOB, but the problem
is we need to use the XMLTYPE object to load and parse the incoming XML
Request ( parameter ). So if I could handle this incoming XML Request via
NCLOB without every introducing CLOB, then I bypass the NLS_CHARACTERSET
entirely and only rely on the NLS_NCHAR_CHARACTERSET which is basically
required to be unicode or AL16UTF16.

Since VARCHAR2 and CLOB datatypes store data in the NLS_CHARACTERSET, the
only way I see to make this work is to actually force the use of AL32UTF8
for the database characterset.

Any ideas or alternative solutions would be appreciated. Maybe this works
in 10g? We are using 9.2.0.4 and 9.2.0.5 currently. Thanks.


From: KJKYLE (AT) COOLBLUE (DOT) COM 13-Oct-04 23:37

Subject: Trying to load NCLOB parameter into XMLTYPE using XMLTYPE.CreateXML


Trying to load NCLOB parameter into XMLTYPE using XMLTYPE.CreateXML


Does anyone one know an alternative to this? We are converting all of our
our column and variable datatypes to NCLOB and NVARCHAR. All of our
procedures consists of parsing an incoming XML request into the XMLTYPE
object. When I change to NCLOB from CLOB, I get type mismatch errors. I
can't believe XMLTYPE is not overloaded to accept NCLOB?

Here is a sample:


CREATE OR REPLACE PROCEDURE TestProc(requestXML IN NCLOB,
responseXML IN OUT NCLOB) AS

xmlRequest XMLTYPE;


BEGIN

xmlRequest := XMLTYPE.CREATEXML(requestXML) ;

END;

Compilation errors for PROCEDURE BMQR.TESTPROC

Error: PLS-00561: character set mismatch on value for parameter 'XMLDATA'
Line: 9
Text: xmlRequest := XMLTYPE.CREATEXML(requestXML) ;

Thanks for any suggestions,
Kurt


--


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com