When is an XQuery Statement too Long?

I just saw Daniel Fink‘s – OptimalDBA – blog post: When is a sql statement too long? When the following OTN XMLDB Forum post popped up in my mind… “xquery” versus “select xmlquery” and passing clauses.

It tells the story about using bind variables and its performance issues, some alternative ways of dealing with things, charactersets and the ORA-19102 error (“XQuery string literal expected”), ORA-19114 (“Error during parsing the XQuery expression: string“) or ORA-01704 (“String literal too long”).

So when is a XQuery string too long…?

If the patch for bug 7317171 has been applied, it looks like the limit is 32K in a AL32UTF8, unicode characterset supporting database. Although metalink note with bug.no 7490566 suggests their is more than meets the eye… 

REPRODUCIBILITY:
----------------
Customer Environments/Results:

OS                               DB Version  NLS_CHARACTERSET  ORA-1704?
-------------------------------- ----------- ----------------- ----------
MS Windows Vista                 10.2.0.3.0  AL16UTF16         No (Works)
MS Windows XP                    11.1.0.6.0  AL16UTF16         No (Works)
Sun Solaris SPARC (64-bit) 5.10  10.2.0.3.0  AL32UTF8          Yes
AIX 5.3 64-bit                   10.2.0.2.0  WE8ISO8859P1      No (Works)

My Inhouse Environments/Results:

OS                               DB Version  NLS_CHARACTERSET  ORA-1704?
-------------------------------- ----------- ----------------- ----------
MS Windows XP Professional sp2   10.2.0.4.0  AL32UTF8          Yes
MS Windows XP Professional sp2   10.2.0.4.0  WE8MSWIN1252      No (Worked)
MS Windows XP Professional sp2   11.1.0.6.0  AL32UTF8          No (Got
                     "LPX-00801: XQuery syntax error at 'EOF'" errorstack)
MS Windows XP Professional sp2   11.1.0.6.0  WE8MSWIN1252      No (Worked)

Don’t think people don’t need that huge amount. People like pretty print stuff so whitespace will eat up more then you might think and people actually create such huge statements as you could have read in the powerpoint shown in my post about “XML Concepts in One Presentation”. Have a look at the presentation from slide 39 and onwards what is started by “A fraction of a real customer XQuery“…

Just so you know.

;-)