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.
😉
