Page 1 of 1
OCIUnicode and XMLTYPE
Posted: Mon 04 May 2009 12:18
by MarkF
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
Posted: Tue 05 May 2009 06:52
by Plash
Editing XMLTYPE is not supported with OCIUnicode = True.
Posted: Tue 05 May 2009 11:34
by MarkF
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
Posted: Wed 06 May 2009 08:34
by Plash
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.
Posted: Wed 06 May 2009 12:39
by MarkF
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
Posted: Thu 07 May 2009 07:19
by Plash
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.
Posted: Wed 15 Sep 2010 21:13
by jdorlon
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?