Can't load NCLOB parameter into XMLTYPE /

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.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