OCIUnicode and XMLTYPE

MarkF · Post by **MarkF** » Mon 04 May 2009 12:18

I'm hitting up against a problem updating or inserting into an XMLTYPE field, but only when OCIUnicode is turned on. I'm using a TOraSmartQuery and then using either .append or .edit as needed, setting the field with .AsString and then Posting.

// This one happens on an update.
Access violation at address 037BB29B in module 'OraOCIEI11.dll'. Write of address 00000000

// This is on an Insert
OCI-22141: given size [17] must be even in UTF-16 environment

In both cases I'm setting the field with .AsString. D2009.

Any suggestions or help are greatly appreciated!

-Mark

Plash · Post by **Plash** » Tue 05 May 2009 06:52

Editing XMLTYPE is not supported with OCIUnicode = True.

MarkF · Post by **MarkF** » Tue 05 May 2009 11:34

Thanks. I've definitely noticed that XMLTYPE usage has gone up a lot in the last few years. It's definitely an important datatype for me. Is the issue related to a bug in Oracle's Unicode OCI layer? If so, is there any possible workaround? Perhaps XMLTYPE could be bound as a string or some other alternative type. Just grasping at straws here since it would be great if this could be made to work somehow.

-Mark

Plash · Post by **Plash** » Wed 06 May 2009 08:34

This is an OCI bug.

Workaround is passing XMLTYPE from/to database as CLOB. You can do it by modifying your SQL query.

We are investigating the possibility of supporting automatic conversion of XMLTYPE to CLOB.

MarkF · Post by **MarkF** » Wed 06 May 2009 12:39

Thanks! I'm trying to workaround it using the following query:

Code: Select all

select 
  rowid, 
  t.id, 
  t.xmlfield.GetCLobVal() XMLFIELD
from XML_TYPE t

But the xmlfield is always marked read only. I had thought that aliasing the column with the original fieldname would fix that, but I must be doing something wrong or perhaps I have a setting wrong on my TSmartQuery. Is this what you meant, and can you see what I'm doing wrong?

-Mark

Plash · Post by **Plash** » Thu 07 May 2009 07:19

You can change the SetFieldsReadOnly option of TOraQuery to False to make all fields editable.

TOraQuery cannot generate the update SQLs automatically for a column based on expression. So you should specify values for the SQLUpdate and SQLInsert properties manually. Use XMLTYPE constructor that takes CLOB parameter in these statements.

jdorlon · Post by **jdorlon** » Wed 15 Sep 2010 21:13

Does this work? I am trying it in the lastest ODAC version with Delphi 2009.

I put this in my TSmartQuery.SQLUpdate TStrings:

Code: Select all

UPDATE "JDORLON"."XML_TYPE"
SET
  ID= :ID,
  XMLFIELD = XMLTYPE(:XMLFIELD)
WHERE
  ROWID = :Old_ROWID

and then when I try to edit the data (using 99 for id and a short XML string for XMLFIELD), this "(" gets put in the XMLFIELD value and of course there is an error.

What am I doing wrong?