It's a repeated issue i guess, but couldn't find a proper solution yet. Basically I am trying to insert bit huge XML i.e. 32000+ characters in to a CLOB column through DB2 procedure. Insertion is failing with the below error looks DB2 is considering the input as String rather than CLOB datatype. Can you please suggest what needs to be done?
SP
CREATE OR REPLACE PROCEDURE logging (IN HEADERDATA CLOB(10M))
LANGUAGE SQL
BEGIN
INSERT INTO Logging(Header) VALUES (HEADERDATA);
COMMIT;
END
Error
The string constant beginning with
"'<?xml version="1.0" encoding="UTF-8"?><XXXXXXXX xmlns:xsi="http:" is too long..
SQLCODE=-102, SQLSTATE=54002, DRIVER=XXXXXX
Character literals in DB2 are limited to about 32K bytes. To handle larger LOBs you need to avoid having to use SQL literal values.
One way to do this without extra programming is write your [future] CLOB contents to a file and use IMPORT or LOAD to insert its contents into a column.
Alternatively, you could wrap a simple Java program around your procedure call where you would use PreparedStatement.setClob() to handle your large XML document.