Oracle Sql Developer "string literal too long" error

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 开发者_StackOverflow中文版-  "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?

---

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

---

You will need to use a CLOB as the input to XMLTYPE() instead of a VARCHAR.

Using either dbms_lob.loadclobfromfile to load the xml from a file, or by breaking up the xml into 32000 character chunks and appending to the CLOB.

DECLARE
   xmlClob CLOB;
BEGIN
/* Build Clob here */

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(xmlClob).extract('/theRange/theRow')))
)
SELECT *
FROM openedxml
WHERE age BETWEEN 30 AND 35;
END;

---

Where does that great big chunk of XML come from ? I assume you are not typing it in.

Generally I'd look at a program that reads the source and turns it into a CLOB. That might be a perl/python/whatever script on a client, or it might be a server side routine that pulls the value from a web-server.

---

You can use sql workaround using insert/updates where each part if less than 4000 chars.

1 Do the insert as an insert with first part is the sql literal up to 4000 chars 2 Do the additional parts as an update concatenating the previous parts with the next part where the next part is up to 4000 chars 3 Repeat step 2 until all the large sql literal is updated.

Example,

Insert into
test_large_literal (col1, col2)
values
(<key val>, <first part of large sql literal>);

update
test_large_literal
set
col2 = col2 || <second part large sql literal>
where
col1 = <key val>;
...
...
update
test_large_literal
set
col2 = col2 || <last part large sql literal>
where
col1 = <key val>;

---

A possible workaround is to use PL/SQL blocks:

DECLARE
  xml VARCHAR2(32000) := 
 '<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>';

  CURSOR C (p1 INTEGER, p2 INTEGER) IS
  SELECT * FROM (
    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(xml).extract('/theRange/theRow'))))
  )
   WHERE age BETWEEN p1 AND p2;
BEGIN
  FOR R IN C (30,35) LOOP
    dbms_output.put_line(R.First||', '||R.Last||', '||R.Age);
  END LOOP;
END;

(Completely untested)

EDIT:

As an insert, you could try:

DECLARE
      xml VARCHAR2(32000) := 
     '<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>';
BEGIN
  INSERT INTO temp_table(last,first,age)
  SELECT last, first, age FROM (
    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(xml).extract('/theRange/theRow'))))
  )
   WHERE age BETWEEN 30 AND 35;
END;