DBMS_XMLGEN package limitations?

Question

I need to get a XML from the returning of a complex query. My previous examples in similar situations, I've used the DBMS_XMLGEN package context tyo generate the XML.

But in this example, I have a problem:

    ...
    begin
    l_ctx :=
             DBMS_XMLGEN.newcontext (
                'SELECT SIRENREC AS receiptNumber,
ICPSCDDE AS companyCode,
             SISENPOL
                AS policyNumber,
             SIREISIN AS claimNumber_aux,
             SUBSTR (SIREISIN, 3, LENGTH (SIREISIN)) AS claimNumber,
      sirevlre - sirevirs + sireviva,
                AS receiptAmount
        FROM MTICPS,
             MTSIRE,
             MTSISE,
             MTINPI,
             MTRLRS              
       WHERE     ICPSIDEN = ''N''
             AND ICPSRFPG = :refPag
             AND ICPSCDDE = SIRECDDE
             AND ICPSISIN = SIREISIN
             AND ICPSNREC = SIRENREC
             AND RLRSCDDE = SIRECDDE
             AND RLRSSISE = SIREISIN
             AND RLRSSIRE = SIRENREC
             AND RLRSISIN = INPIISIN
             AND RLRSRECI = INPINREC
             AND RLRSCDDE = INPICDDE
             AND SIRECDDE = SISECDDE
             AND SIREISIN = SISEISIN
             AND SIREVLRE <> 0
             and rownum < 110
          ORDER BY SIRECDDE, SIREISIN, SIRENREC');
      DBMS_XMLGEN.setrowsettag (l_ctx, 'INT_DATA');
          DBMS_XMLGEN.setrowtag (l_ctx, 'Item');

          DBMS_XMLGEN.setbindvalue (l_ctx, 'refPag', 'BLABLA');
    lxmltype := DBMS_XMLGEN.getXmlType (l_ctx);

          DBMS_XMLGEN.closeContext (l_ctx);

My problem here, I think, is the size. For exemple, if I put a and rownum < 110 condition, I can get all the XML and completed for 110 records. But if I have more than 110 records, my l_ctx variable is simply empty and my XML is empty.

We have some limitations here? I've looked up in Google and Oracle documentation but I was not able to find any reference.

Any help? Thanks. Filipe

Edit1: I've found that I have a numeric or value error when converting the XMLTYPE to CLOB. I think my XMLTYPE is not null. It is just a large variable and I can't convert it to CLOB using xmltype.getClobVal();

score 3 · Accepted Answer · answered Oct 26 '15 at 16:34

3

Well.. I've found the error. It was the conversion from XMLTYPE to CLOB. For large XMLTYPE variables, the XMLTYPE.getClobVal() has some kind of size limitation. So, I used XMLSerialize function to do the conversion: SELECT XMLSERIALIZE (DOCUMENT lxml_xmltypevar) FROM DUAL;

Thanks for anything.

answered Oct 26 '15 at 16:34

milheiros

621
2
14
34

For other users what Oracle version are you using? – kevinskio Oct 26 '15 at 16:34
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit – milheiros Oct 30 '15 at 16:05
1

Thanks for answering your own question, I had the same problem. – Peter Apr 03 '17 at 08:19

DBMS_XMLGEN package limitations?

1 Answers1