Editing non-XML columns in tables with XMLTYPE

[jdorlon]

Hello,

Suppose I have this table:

Code: Select all

CREATE TABLE XMLTEST
(
  NUM_COL       NUMBER,
  VARCHAR2_COL  VARCHAR2(30),
  XMLTYPE_COL   SYS.XMLTYPE
)
XMLTYPE XMLTYPE_COL STORE AS SECUREFILE BINARY XML;

I have OraCall.OCIUnicode = true, so when I select from this table in a TSmartQuery, I have to do it this way to prevent an error due to binary XML and OCIUnicode:

Code: Select all

SELECT 
   ROWID, X.NUM_COL, X.VARCHAR2_COL, X.XMLTYPE_COL.GetClobVal() as XMLTYPE_COL
FROM XMLTEST X

This works fine, but when it comes time to edit or delete a row using a TDBGrid, the following query is generated by ODAC, which leads to an AV because it tries to fetch the XMLTYPE data as XML and not a CLOB.

Code: Select all

SELECT * FROM "JDORLON"."XMLTEST"
WHERE
  "ROWID" = :"Old_ROWID"
FOR UPDATE NOWAIT

It seems to me that the only reason you are performing this query is to see if there is a lock on the row before the edit or delete is made. You don't really need the row data. So I tried changing it to this:

Code: Select all

SELECT 'x' FROM "JDORLON"."XMLTEST"
WHERE
  "ROWID" = :"Old_ROWID"
FOR UPDATE NOWAIT

and my problem went away. Is this a safe change? If so, will you include it in the next release? Besides preventing the AV in some cases, it should also be faster in most other cases, since it will save time of fetching row data that isn't going to be used anyway.

Thanks,

John Dorlon

[AlexP]

hello,

Thank you for the information. We have reproduced the problem. We will review the suggested fix and try to correct this problem till the next ODAC build.