Issue with XMLTYPE and OCIUnicode mode.

[jdorlon]

Hello,

I am getting pressure again from my customers about XMLTYPE Support.

Oracle Metalink doc ID 1296105.1 says that we can use these LOB OCI calls for XMLType. Have you tried this?

• OCILobLocator
• OCIDescriptorAlloc
• OCILobCreateTemporary
• OCILobWrite

Is there a change that I can make in ODAC so that XMLTYPE is treated as CLOBs?

Thanks,

-John

[AlexP]

We have already fixed the problem. The fix will be included in the next build.

[jdorlon]

That's great news! I can't wait to try it out! Thanks!

[AlexP]

The new version with this fix is already available for download at our website

[jdorlon]

Should XMLTYPE with Binary Storage work too? I just installed 9.7.28 and I still get an AV when doing a "select *" from the following table in a TSmartquery:

My TOraSession has UseUnicode=True and UnicodeEnvironment=True.

Code: Select all

SET DEFINE OFF;

CREATE TABLE HAS_XML_BIN
(
  NUM_COL  NUMBER,
  XML_COL  SYS.XMLTYPE
)
XMLTYPE XML_COL STORE AS SECUREFILE BINARY XML (
  TABLESPACE  USERS
  ENABLE      STORAGE IN ROW
  CHUNK       8192
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
ALLOW NONSCHEMA
DISALLOW ANYSCHEMA;


INSERT INTO HAS_XML_BIN
   (NUM_COL, XML_COL)
 VALUES
   (1, '<MANAGER>
  <APP vault="true" class="TarActionSet">Action Recall</APP>
  <APP vault="false" class="TarActionSet">Sample Dev App<ACTION class="TarIfThenElse">Check Server<IF>
        <ACTION class="TarTNSPing">Check Server is up</ACTION>
      </IF>
      <THEN>
        <ACTION class="TarCompareSchemas">Compare Schemas</ACTION>
        <ACTION class="TarExportDDL">Export Dev Schema Objects</ACTION>
        <ACTION class="TarZipArchive">Zip DDL Files</ACTION>
        <ACTION class="TarSaveGrid">Export Customers Data</ACTION>
        <ACTION class="TarEmailAction">email success</ACTION>
      </THEN>
      <ELSE>
        <ACTION class="TarEmailAction">email failure</ACTION>
      </ELSE>
    </ACTION>
  </APP>
  <APP vault="false" class="TarActionSet">Sample DBA App<ACTION class="TarIfThenElse">Check Servers<IF>
        <ACTION class="TarTNSPing">Check Server is up</ACTION>
      </IF>
      <THEN>
        <ACTION class="TarExecuteScript">Daily Checks</ACTION>
        <ACTION class="TarObjectSearch">Export quest_prod objects</ACTION>
        <ACTION class="TarDBHealthCheck">Database Health Check</ACTION>
        <ACTION class="TarCompareSchemas">Compare Prod vs Dev</ACTION>
        <ACTION class="TarSchemaDoc">Daily Schema Update</ACTION>
        <ACTION class="TarListIterator">User List<ACTION class="TarEmailAction">email multiple users</ACTION>
        </ACTION>
      </THEN>
      <ELSE>
        <ACTION class="TarEmailAction">No Reports</ACTION>
      </ELSE>
    </ACTION>
  </APP>
  <APP vault="false" class="TarActionSet">App1</APP>
  <APP vault="false" class="TarActionSet">Sample Code Quality App<ACTION class="TarIfThenElse">Check Servers<IF>
        <ACTION class="TarTNSPing">Check Server is up</ACTION>
      </IF>
      <THEN>
        <ACTION class="TarCXAction">Run Code Analysis</ACTION>
        <ACTION class="TarCodeTester">Run Code Tester Tests</ACTION>
        <ACTION class="TarDBHealthCheck">Database Health Check</ACTION>
        <ACTION class="TarListIterator">User List<ACTION class="TarEmailAction">email multiple users</ACTION>
        </ACTION>
      </THEN>
      <ELSE>
        <ACTION class="TarEmailAction">No Reports</ACTION>
      </ELSE>
    </ACTION>
  </APP>
</MANAGER>
');
INSERT INTO HAS_XML_BIN
   (NUM_COL, XML_COL)
 VALUES
   (3, '<?xml version="1.0" encoding="UTF-8"?>
<PMFilterHistory/>
');
INSERT INTO HAS_XML_BIN
   (NUM_COL, XML_COL)
 VALUES
   (4, '<?xml version="1.0" encoding="UTF-8"?>
<FilterHistory>
  <Filter Type="15">*</Filter>
</FilterHistory>
');
COMMIT;

The AV occurs on this line in TOraXML.Read:

Check(OCI8.OCIPStreamRead(OCISvcCtx.hOCIError, phOCIDescriptor, Dest, Len, 0));

[AlexP]

Thank you for the information - we have reproduced the problem and will try to fix it in the next version

[MarkF]

Are there any updates on this? It was nice to see that it is being worked on. Thanks as always.

[MarkF]

Any hope for this ever being fixed? My original report was in 2006 (11 years ago.) I just renewed my ODAC license and the 50% increase in price would go down easier if it came with this fix. Thanks.

[MaximG]

The latest ODAC 10.1.3 version includes required fixes to work with XMLTYPE in the Direct Mode. To implement the described behavior in OCI Mode, changes should be made to Oracle Client itself, you can contact this product technical support for these changes.

[MarkF]

Thanks for replying. Is there any thought to trying to get this working for OCI mode? Obviously the two options are:

1. Getting Oracle to fix the bug in OCIPStreamRead (which appears to be an undocumented function?)
2. Stop using OCIPStreamRead and investigate other methods of retrieving XMLType.

I suspect that #2 is the way to go, has anything been attempted along those lines? It seems like Oracle can't be relying on an undocumented function as the only way to get XMLType from the database.

Added note: I see that oci.dll exports a function called OCIPStreamFromXMLType2. It's a total shot in the dark, but maybe it has some difference in setting up the stream? Might be worth a sleuthing.

[MaximG]

You can get a description of the work of the undocumented functions you are interested in by contacting Oracle tecnical support

[MarkF]

I appreciate the suggestion that I should do it for you. Well played.

[MaximG]

We will consider the possibility of using the specified OCI functions and let you know about the results as soon as we get them