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 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 try to start 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 lines will sometimes be longer than 4000 characters. Any ideas on how I can get around this problem?

Answer 1

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

Using either dbms_lob.loadclobfromfile to load the xml from the file, or by breaking the xml into 32,000 characters and adding 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; 

Answer 2

You cannot get around this with "plain" SQL. (But I would be happy to be mistaken)

To do this, you will need some kind of programming language (for example, Java, a stored procedure).

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

This is one of the limitations of Oracle that really turns me on. 20 years ago, this might have been somewhat acceptable, but for now ...

Answer 3

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 can 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; 

Answer 4

Where does this big piece of XML come from? I assume you are not printing it.

In general, I would look at a program that reads a source and turns it into a CLOB. It can be perl / python / whatever script on the client, or it can be a server side procedure that retrieves the value from the web server.

Answer 5

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

1 Insert an insert with the first part of the letter sql up to 4000 characters 2 Make additional parts as an update, connecting the previous parts to the next part where the next part has up to 4000 characters 3 Repeat step 2 until the whole 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>;