This forum is currently locked. You can't register or post questions at this time. (read more)

XMLForest

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

rcb
Member
Posts: 9
Joined: Tue Sep 11, 2012 9:52 am

XMLForest

Postby rcb » Thu Sep 26, 2013 12:51 pm

Dear Tim,

for an application interface we have to xmlize data from associated tables. We use XMLAgg, XMLElement & Co. to generate the XML documents. To map the data from the fact table into the XML a PLSQL function inside the SQL is used. The PLSQL function is used inside a XMLForest.

During tests I recognized that the PLSQL function is called twice as much as it should.

I set up a small example (table / table_column):

Code: Select all

CREATE TYPE zz_xmltype_tt IS TABLE OF XMLTYPE;
/

CREATE OR REPLACE PACKAGE zz_testxml AS

    FUNCTION Col2Xml( sOwner VARCHAR2, sTableName VARCHAR2) RETURN XMLTYPE;

    PROCEDURE Table2Xml_1( sTableName VARCHAR2);
    PROCEDURE Table2Xml_2( sTableName VARCHAR2);

END zz_testxml;
/


CREATE OR REPLACE PACKAGE BODY zz_testxml AS

    Col2Xml_called BINARY_INTEGER;

    /* Test function to xmlize some fact data */
    FUNCTION Col2Xml( sOwner VARCHAR2, sTableName VARCHAR2) RETURN XMLTYPE
    IS
        xt XMLType;
    BEGIN
        Col2Xml_called := Col2Xml_called + 1;
   
        SELECT XMLAgg(XMLForest(column_name, data_length))
        INTO xt
        FROM dba_tab_columns
        WHERE owner=sOwner AND table_name=sTableName;
       
        RETURN xt;
    END Col2Xml;

    /* Use test function without XMLForest */
    PROCEDURE Table2Xml_1( sTableName VARCHAR2)
    IS
        xtt ZZ_XMLTYPE_TT;
    BEGIN
        Col2Xml_called := 0;
   
        SELECT XMLElement("Columns",Col2Xml(owner, table_name))
        BULK COLLECT INTO xtt
        FROM dba_tables
        WHERE table_name=sTableName;

        dbms_output.put_line('Col2Xml_called: '||Col2Xml_called);
        dbms_output.put_line(SUBSTR(xtt(1).getStringVal(),1,250));
    END Table2Xml_1;

    /* Use test function within XMLForest */
    PROCEDURE Table2Xml_2( sTableName VARCHAR2)
    IS
        xtt ZZ_XMLTYPE_TT;
    BEGIN
        Col2Xml_called := 0;

        SELECT XMLForest(owner,table_name,tablespace_name,Col2Xml(owner, table_name) AS "Columns") AS "Table"
        BULK COLLECT INTO xtt
        FROM dba_tables
        WHERE table_name=sTableName;
       
        dbms_output.put_line('Col2Xml_called: '||Col2Xml_called);
        dbms_output.put_line(SUBSTR(xtt(1).getStringVal(),1,250));
    END Table2Xml_2;

END zz_testxml;
/


Execution:

Code: Select all

db> begin zz_testxml.Table2XML_1('CDC_CHANGE_TABLES$'); end;
  2  /
Col2Xml_called: 1
<Columns><COLUMN_NAME>OBJ#</COLUMN_NAME><DATA_LENGTH>22</DATA_LENGTH><COLUMN_NAME>CHANGE_SET_NAME</COLUMN_NAME><DATA_LEN
GTH>30</DATA_LENGTH><COLUMN_NAME>SOURCE_SCHEMA_NAME</COLUMN_NAME><DATA_LENGTH>30
</DATA_LENGTH><COLUMN_NAME>SOURCE_TABLE_NAME</COLU

PL/SQL-Prozedur erfolgreich abgeschlossen.

db> begin zz_testxml.Table2XML_2('CDC_CHANGE_TABLES$'); end;
  2  /
Col2Xml_called: 2
<OWNER>SYS</OWNER><TABLE_NAME>CDC_CHANGE_TABLES$</TABLE_NAME><TABLESPACE_NAME>SYSTEM</TABLESPACE_NAME><Columns><COLUMN_N
AME>OBJ#</COLUMN_NAME><DATA_LENGTH>22</DATA_LENGTH><COLUMN_NAME>CHANGE_SET_NAME<
/COLUMN_NAME><DATA_LENGTH>30</DATA_LENGTH><COLUMN_

PL/SQL-Prozedur erfolgreich abgeschlossen.

db>


I wonder why the function Col2Xml is called twice for one table row when it is used inside a XMLForest (Table2Xml_2). Procedure Table2Xml_1 worked like expected (one call to Col2Xml ).

It would be great if you could explain the difference. The use of XMLForest ist quite handy, but the watched behavior might decrease the performance.

Thanks,
Ralph

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XMLForest

Postby Tim... » Thu Sep 26, 2013 6:54 pm

Wow! That looks like a bug to me. I've just run your example in 12c and it is the same there.

You should raise this as a bug with Oracle Support and see what they come back with.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mbobak
Member
Posts: 1
Joined: Sun Dec 01, 2013 5:51 am

Re: XMLForest

Postby mbobak » Sun Dec 01, 2013 6:21 am

Hi Tim, Hi Ralph,

I just discovered this forum. Looks like this posting is a couple of months old.

Ralph,

Did you open an SR with Oracle? Was it accepted as a bug?

My company has been working with Oracle on XMLDB for some time, and as a result, I have some really good contacts inside the XMLDB development team. If you want, I can make sure this issue gets in front of the right people inside the development team.

Sorry I didn't see this earlier, but, better late than never.... :-)

-Mark

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XMLForest

Postby Tim... » Mon Dec 02, 2013 6:59 am

Hi.

I think the issue is probably a wider one. I noticed that function calls when combined with the query result cache result in the query being run twice. I thought this was a dynamic sampling issue, but it's not. This is repeatable in both 11g and 12c.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

rcb
Member
Posts: 9
Joined: Tue Sep 11, 2012 9:52 am

Re: XMLForest

Postby rcb » Tue Apr 08, 2014 8:22 am

Hi Tim, Hi Mark,

sorry, I lost sight of this issue.

There was already a SR with Oracle regarding this issue. It was accepted as Bug 13991130 : XMLFOREST - INVOKING FUNCTION MULTIPLE TIMES.

Finally the problem is going to be fixed starting with the patchset: 12.1.0.2.0.

Kind regards,
Ralph

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XMLForest

Postby Tim... » Tue Apr 08, 2014 8:35 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron