Toad World® Forums

Code Tester Repository - XMLTYPE fields now being stored as BINARY XML as of 11.2.0.2 - instance crashing on reading large documents


#1

Hi,

I have found an issue with XMLTYPE columns used in the code tester repository that is causing our database instance to crash.

Our database is on 11.2.0.2 which is unfortunate as Oracle changed the default storage to XMLTYPE to BINARY in that version and there is no easy way to change this default behaviour at present.

See:https://forums.oracle.com/message/4584535#4584535 and MOS note: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=1207893.1

Searching on Oracle Support found nothing however on one of the OTN forums somebody had got an issue with storing XML documents larger than 32k in the new binary format.

As a test I modified the XML columns to use CLOB storage and everything worked fine.

For now we are faced with the following solutions:

a) Install the repository in 11.2.0.1 or earlier

b) Try and get Oracle to fix the bug

c) Modify the code tester installation scripts to store the XMLTYPE fields as CLOBs instead of binary.

Any thoughts would be much appreciated.

Thanks

Tony


#2

Hi Tony,

This is a new issue. At least for me…

I have a couple of questions for you:

  1. How big your XMLTYPEs, which are the reason of the craches?
  2. In order to shorten our investigations could you recommend some documents/articles explaining how to create these new XMLTYPE columns?
    Thank you,

Leonid Khukhlovich,

Code Tester Team


#3

Hi Leonid,

After some more investigation I think the issue is the conversion of the XML data back to clob from binary - there was a post from Stephen Fuerstein a while ago about the getclobval() member function causing an ORA-03113 and having to write a wrapper function gcv to work around this.

The XMLTYPE column is P_VALUE QU_ATTRIBUTES table in the repository.

This particular value is 225912 bytes.

I replicated the issue by just selecting the single column from the QU_ATTRIBUTES table - running this in Toad also causes the instance failure:

SELECT QU_ATTRIBUTES_QP.gcv(q.DP_VALUE)

FROM QU_ATTRIBUTES Q

WHERE Q.PARENT_GUID = ‘{23C113BB-1594-40F7-98CF-A82F6AB8C6EB}’

AND Q.USAGE_TYPE = ‘CONTROL’;

There is an Oracle Support note on the change:

support.oracle.com/…/DocumentDisplay

Also in the OTN forums:

forums.oracle.com/…/10982043

Here is a small example that produces a 200k XML file in each of the storage types, running the SQLs in Toad will cause the instance crash:

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

DROP TABLE T3 PURGE;

REM Default storage

CREATE TABLE T1(ID NUMBER, DATA XMLTYPE);

REM Store as CLOB

CREATE TABLE T2(ID NUMBER, DATA XMLTYPE) XMLTYPE DATA STORE AS CLOB;

REM Store as BINARY XML

CREATE TABLE T3(ID NUMBER, DATA XMLTYPE) XMLTYPE DATA STORE AS BINARY XML;

SELECT TABLE_NAME,STORAGE_TYPE,COLUMN_NAME FROM USER_XML_TAB_COLS WHERE TABLE_NAME IN (‘T1’,‘T2’,‘T3’);

INSERT INTO T1(ID,DATA)

SELECT 1,XMLELEMENT(“ROWSET”,XMLAGG(XMLELEMENT(“DATA”,RPAD(TO_CHAR(ROWNUM,‘FM000000000000000000’),‘200’,’#’)))) FROM

(SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10) L1,

(SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10) L2,

(SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10) L3;

COMMIT;

INSERT INTO T2(ID,DATA) SELECT ID,DATA FROM T1;

COMMIT;

INSERT INTO T3(ID,DATA) SELECT ID,DATA FROM T1;

COMMIT;

REM Running T1 or T3 in Toad causes the instance to crash

SELECT T.DATA.GETCLOBVAL() FROM T1 T;

SELECT T.DATA.GETCLOBVAL() FROM T2 T;

SELECT T.DATA.GETCLOBVAL() FROM T3 T;

Thanks

Tony


#4

Hi Tony,

Thank you for your examples and references. I studied them and now understand the problem. I will prepare new scripts for you soon.

Did you create a case in our Support department?

If you do it will be easier for us to accomplish all needed procedures in order to add these changes to next Code Tester patch.

Thanks,

Leonid


#5

Hi Leonid,

Many thanks for that.

We have not created a support case as we are evaluating a trial version of the tester and are about to commit to a purchase. Once we have obtained the license then a support case can be raised.

Regards,

Tony


#6

Hi Tony,

I think I am done with my changes. I am sending you 2 scripts and instructions by e-mail.

These changes will be a part of our next patch. So you don’t need to create a support case anymore.

Thanks,

Leonid


#7

Tony, send me please you preffered e-mail address for communications to leonid.khukhlovich@quest.com