Oracle DBMS_XMLGEN.newcontextfromhierarchy (-> queryString)

Question

I have a question and maybe someone has gone through the same problem and can help. I'm working on a project where I need to create a hierarchical XML document from hierarchical data. I have the query that pulls the data and the problem I'm facing is that the query is too long for the querystring parameter accepted by DBMS_XMLGEN.newcontextfromhierarchy (queryString varchar2)

I tried using a with clause but that was not recognized when selecting from the resultset which looks understandable.

  SELECT XMLRoot( XMLELEMENT
          ("clients",
           (SELECT DBMS_XMLGEN.getxmltype
                      (DBMS_XMLGEN.newcontextfromhierarchy
                          ('SELECT level,
              XMLElement("client",
                         XMLElement("client_number", client_number),
                         XMLElement("parent_client_number", parent_client_number),
                         XMLElement("level", level),
                         XMLElement("client_level", client_level))
         FROM clients
  START WITH parent_client_number = ''900002''
  CONNECT BY PRIOR  client_number = parent_client_number
  ORDER siblings BY parent_client_number'))
              FROM DUAL), (XMLELEMENT())),VERSION '1.0') as XMLDATA
  FROM DUAL;

The query is much longer than the one above but that will give an idea of what I'm running.

This is the exact error I'm getting.

ORA-01704: string literal too long

Has anyone faced this problem before and have any ideas of how to overcome it?

Thanks, Sergio

Answer 1

Yeah, always a problem when code has to be in a VARCHAR2 with length limitations on it :-)

If DBMS_XMLGEN.newcontextfromhierarchy had accepted a CLOB, the solution would have been to build the CLOB in lumps of several literals that each was smaller than the limit. But DBMS_XMLGEN.newcontextfromhierarchy unfortunately uses a VARCHAR2.

Classic solution would be to write the query as a view and then the string could simply be "select * from myview where ..." Unfortunately you kind of need a "parameter" to the view to put in the START WITH clause.

One way to do that could be to use a context as described in #1 in this link: http://mahmoudoracle.blogspot.dk/2012/06/create-view-with-parameter.html#.VAVdNPmSwt0 .

Create a context to hold the parameter(s) (see the link).

Define the view something like:

select ...
from ...
start with SYS_CONTEXT ('MY_CTX', 'parm1')
...

Just before your DBMS_XMLGEN call, set the parameter:

CTX_PKG.SET_CONTEXT ('parm1', '900002');

And then the query string would just be:

SELECT * FROM MY_VIEW

This method could handle as long a query as it is possible to build into a view (which is a lot, I don't know how much, but a lot ;-)

If the greater part of your code is the XMLELEMENT("client"...) part, an alternative (and perhaps simpler) solution would be something like:

CREATE OR REPLACE FUNCTION MyXml (
   p_client_number        clients.client_number%type,
   p_parent_client_number clients.parent_client_number%type,
   ...
)
   RETURN xmltype
IS
   RETURN XMLELEMENT("client"...);
END;

And your query in the DBMS_XMLGEN call would become something like:

DBMS_XMLGEN.newcontextfromhierarchy(
   'SELECT level,
 MyXml(client_number, parent_client_number, ...) the_xml
 FROM clients
 START WITH parent_client_number = ''900002''
 CONNECT BY PRIOR  client_number = parent_client_number
 ORDER siblings BY parent_client_number')

Just moving all the XML generation out to a function might make your code small enough, it would depend on whether there are other tables or joins or the number of parameters to the function would be too large in itself.

So it can be a choice of methods - if the function makes the query "small enough", it is the simpler method. If the function is not enough, the parameterized view should be able to handle it - it is just a little more "unclear what happens" to future managers of this code ;-)

Answer 2

Thanks Kim, you pointed me in the right direction. The biggest part of the select was the query I was using to select from. I fixed the problem by defining a record object and creating a table of those records, opening an external cursor selecting into the table of objects and then cast this into a table within the sql query string in DBMS_XMLGEN.newcontextfromhierarchy(). If anyone faces a similar problem this might help.

The function was declares as part of the package and had to be included in the package specification.

CREATE OR REPLACE TYPE client_record AS OBJECT(

      x1         varchar2(8 Byte),
      x2          varchar2(3 Byte),
      x3  varchar2(8 Byte),
      x4            varchar2(22 Byte),
      x5         CHAR(1)
  );

CREATE OR REPLACE TYPE client_rec_tab IS TABLE OF client_record;
/


 FUNCTION get_merchant_hierarchy(x1 IN VARCHAR2,
                                 x2 IN VARCHAR2
 ) RETURN client_rec_tab 

 IS some code to open cursor and bulk fetch into a client_rec_tab ...

 END get_merchant_hierarchy;


        DBMS_XMLGEN.newcontextfromhierarchy
                          ('SELECT level,
             XMLElement("client_details",
                         XMLElement("client_number", client_number),
                         XMLElement("client_level", client_level),
                         XMLElement("parent_client_number", parent_client_number),
                         XMLElement("trade_name", trade_name),
                         XMLElement("billing_level", billing_level))
         FROM table(package.get_merchant_hierarchy(:1,:2))
  START WITH client_number = (select client_number from table(package.get_merchant_hierarchy(:1,:2)) where client_level = ''002'')
  CONNECT BY PRIOR client_number = parent_client_number
  ORDER siblings BY parent_client_number');