8 Replies Latest reply: Aug 15, 2008 3:40 AM by AntsHindpere RSS

    XMLQuery() function: xquery string max length?

    625055
      Is there a maximum length for a xquery passed to the XMLQuery() function?

      I have a very complex xquery of about 15000 characters. It works fine in Berkeley XML DB. I'm trying to execute it by the Oracle query:

      select xmlquery('...my xquery...' returning content) from dual

      Executing this query in Oracle SQL Developer, it returns the error
      "SQL Error: ORA-19114: XPST0003 - error during parsing the XQuery expression: "
      followed by a part of my xquery with the end truncated.

      Executing the query from my application (ASP.NET 2.0), the returned error is:
      "Oracle exception message: ORA-03113: end-of-file on communication channel"

      Reducing the length of the xquery, removing some returned nodes, the xquery works fine. I've done various tests, alternating the removed nodes: the xquery result depends by the xquery length, not by the returned nodes or the xquery syntax.
      I'm sure that the syntax of my xquery is correct. I'm trying it in Berkeley XML DB, at the same time.

      If there is a limit to the length of the xquery string, how can I execute my query?
      Or I cannot use Oracle DB XML in my application?


      Thank you.
      Mirko
        • 1. Re: XMLQuery() function: xquery string max length?
          mdrake-Oracle
          If the query is long you'll need to supply it as a bind variable.

          One way to do this is to store the XQuery as a document in the Oracle XML DB repository and then to bind it into the Query statements

          Eg
          declare
            res boolean;
          begin
            res := dbms_xdb.createResource('/public/q1.xqy','My Xquery');
            commit;
          end;
          /
          select xmlquery(xdburitype('/public/q1.xqy').getClob() passing OBJECT_VALUE returning content) from MY_XML_TABLE
          • 2. Re: XMLQuery() function: xquery string max length?
            625055
            My query is created dinamically.
            Have I to create a temporary resource or there is another way?

            What's the maximum length of a xquery?

            Thank you!

            Mirko
            • 3. Re: XMLQuery() function: xquery string max length?
              625055
              Please, does someone know what's the maximum length of a xquery?
              • 4. Re: XMLQuery() function: xquery string max length?
                442193
                Dear Mark Drake,

                You mention:

                If the query is long you'll need to supply it as a bind variable.

                One way to do this is to store the XQuery as a document in the Oracle XML DB repository and then to bind it into the Query statements

                Eg

                declare
                res boolean;
                begin
                res := dbms_xdb.createResource('/public/q1.xqy','My Xquery');
                commit;
                end;
                /
                select xmlquery(xdburitype('/public/q1.xqy').getClob() passing OBJECT_VALUE returning content) from MY_XML_TABLE


                I tried to do this but the xmlquery only seems to accept a VARCHAR2. And the bind variable in your example is returned as a CLOB.

                Is there something else you need to do to make the bind variable acceptable?

                Database is version 10.2.0.4

                Kind regards,

                Robbie
                • 5. Re: XMLQuery() function: xquery string max length?
                  442193
                  Still trying to get this to work :-).

                  Has anyone else encountered this issue?

                  When I execute 'select xdburitype('/public/q1.xqy').getClob() from dual' I can see the query, so this works fine.
                  Bust when passing it to the xmlquery function, it can not be executed.
                  I get ORA-19102 - XQuery string literal expected.

                  Can it be that it has something to do with the ACL settings on the resource?
                  • 6. Re: XMLQuery() function: xquery string max length?
                    AntsHindpere
                    Hi,

                    it works in 11g not in 10g.
                    SQL> declare
                      2  res boolean;
                      3  begin
                      4  res:=dbms_xdb.createResource('/public/q1.xqy','1');
                      5  end;
                      6  /
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> select xmlquery(xdburitype('/public/q1.xqy').getclob() returning content) val from dual;
                    
                    VAL                                                                             
                    --------------------------------------------------------------------------------
                    1                                                                               
                    Ants
                    • 7. Re: XMLQuery() function: xquery string max length?
                      mdrake-Oracle
                      Note that when ever possible it is preferred to supply the XQuery as a string literal rather than as a Bind Variable. This allows us to do a much better job of optimzing the query since the query is avaialbe at the point the SQL query is parsed and compiled, rather than at execution time. The ability to pass the XQuery as a bind variable should only be used as a last resort since this means the XQuery is not know until execution time, and, in theory could be different for each iteration of the statement, making any kind of optimziation virtually impossible.

                      In 11g it should be possible to pass up to 32K (in a non AL32UTF8 database). The limit for an AL32UTF8 database will also be 32K once the patch for bug 7317171 has been applied. If people think taht they need to create large XQuery's than 32k please post here and we will consider what can be done to raise the limitation in a subsequent release or patch.

                      Note that even if you are constructing the XQuery dynamically is more efficient to construct the SQL statement containing the XQuery dynamically than to bind the XQuery into a static SQL Statement. The ability to late bind the XQuery as CLOB should only be used when it is absolutely impossbile to code the XQuery as a static string.
                      • 8. Re: XMLQuery() function: xquery string max length?
                        AntsHindpere
                        Hi,

                        In 11g it should be possible to pass up to 32K (in a non AL32UTF8 database)...

                        I was able to pass 66K, db version 11.1.0.6.0
                        SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
                        
                        VALUE                                                                           
                        ----------------------------------------                                        
                        WE8MSWIN1252                                                                    
                        
                        Elapsed: 00:00:00.01
                        SQL> declare
                          2  res boolean;
                          3  begin
                          4  dbms_output.put_line('File q1.xry size='||dbms_lob.getlength(bfilename('XMLDIR','q1.xry')));
                          5  res:=dbms_xdb.createresource('/public/q1.xry',bfilename('XMLDIR','q1.xry'));
                          6  dbms_output.put_line('File q2.xry size='||dbms_lob.getlength(bfilename('XMLDIR','q2.xry')));
                          7  res:=dbms_xdb.createresource('/public/q2.xry',bfilename('XMLDIR','q2.xry'));
                          8  end;
                          9  /
                        File q1.xry size=66962                                                          
                        File q2.xry size=66963                                                          
                        
                        PL/SQL procedure successfully completed.
                        
                        Elapsed: 00:00:00.19
                        SQL> select xmlquery(xdburitype('/public/q1.xry').getclob() returning content) from dual;
                        
                        XMLQUERY(XDBURITYPE('/PUBLIC/Q1.XRY').GETCLOB()RETURNINGCONTENT)                
                        --------------------------------------------------------------------------------
                        11111111111111111111111111111111111111111111111111111111111111111111111111111111
                        
                        Elapsed: 00:00:01.12
                        SQL> select xmlquery(xdburitype('/public/q2.xry').getclob() returning content) from dual;
                        ERROR:
                        OCI-31011: XML parsing failed 
                        
                        
                        Elapsed: 00:00:00.45
                        I searched bug id 7317171 in metalink and it didn't returned no hits, I guess it's not published for customers.

                        Ants