CODEWITHSUNDEEP
plsqloracle11gclobvarchar2
Mahesh Jagtap
Mahesh Jagtap

Reputation: 128

Using CLOB instead of VARCHAR2

We want to create an XML. The current code does it by appending one XML tag at a time to a VARCHAR2 variable.

xmlString                   VARCHAR2(32767);
....
....
xmlString := xmlString || '<' || elementName || '>' || elementValue || '</' || elementName || '>';

However due to size limitation of 32767 characters on VARCHAR2, we get the following error for a very long XML. 

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The solution we have is to declare a CLOB and write a procedure to keep flushing the VARCHAR2 variable to the CLOB. 

v_result                    clob;
.....
.....
IF xmlString IS NOT NULL THEN
        dbms_lob.writeappend( v_result, LENGTH(xmlString), xmlString);
        xmlString := NULL;
END IF;

However this would require replacing lots of exiting code lines with calls to the new function. Is there a better way to do this?

Anything similar to Operator Overloading in PLSQL? Can I change the data type of xmlString variable to CLOB and make the || operator to do the work of dbms_lob.writeappend?

Upvotes: 0

Views: 797

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Yes, if you change the data type of xmlString to clob, the string concatenation operator will continue to work.

However, building XML this way would be a very poor architecture. That's the sort of architecture that has a high probability of generating invalid XML when, for example, one of the strings happens to have a character that needs to be escaped (or for any of a number of different reasons). Oracle provides a whole host of functions to generate XML (XMLElement, XMLForest, SYS_XMLGen, DBMS_XMLQuery, etc. depending on your use case). It would be far better architecturally to use those built-in functions.

Upvotes: 4

Related Questions