Oracle Sql Developer "string literal too long" error

Question

I have the following SQL that I would like to run in Oracle SQL Developer against an Oracle 10g server:

WITH openedXml AS (
  SELECT extractvalue(column_value, '/theRow/First') FIRST,
         extractvalue(column_value, '/theRow/Last') LAST,
         to_number(extractvalue(column_value, '/theRow/Age')) Age
    FROM TABLE(XMLSequence(XMLTYPE('
  <theRange>
    <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow>
    <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow>
...
...
...
    <theRow><First>Tom</First><Last>Anderson</Last><Age>39</Age></theRow>
    <theRow><First>Ali</First><Last>Grady</Last><Age>45</Age></theRow>
  </theRange>
  ').extract('/theRange/theRow')))
)
SELECT *
FROM openedxml
WHERE age BETWEEN 30 AND 35;

When I attempt to run it I get the following error:

Error at Command Line:1 Column:0 Error report: SQL Error: ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.

My strings will occasionally be much longer than 4000 characters. Any ideas about how I can get around this problem?

Answer 1

You can't get around this with "plain" SQL. (But I'd be glad to be proven wrong)

You will need some kind of programming language (e.g. Java, Stored Procedure) to deal with this.

An alternative is to upload the XML data into a table (can be done with SQL*Loader) and the use the column values in your query.

This is one of the limitations of Oracle that is really driving me nuts. 20 years ago this might have been somewhat acceptable, but nowadays...