Inserting large XML document into Oracle-CodePudding

I have a table in Oracle:

CREATE TABLE foo (id NUMBER, document XMLType)

and I would typically insert data into it with SQL:

INSERT INTO foo VALUES (1, XMLType('<parent><child></child></parent>'))

However, I now have a document which is more than the maximum size for a string literal in PL/SQL (4000 characters). Is it possible to insert such a document in a single SQL script, without resorting to writing it to a file and then referencing that file in the sql... as that option is not open to us for reasons. I have tried:

inserting 4000 characters, then appending the rest in chunks - this does not work as the intermediate steps fail XML validation
temporarily changing the column type to CLOB etc. - Oracle forbids the conversion of columns between major types.

CodePudding user response：

In SQL, concatenate 4000 byte strings to EMPTY_CLOB():

INSERT INTO foo (id, document)
VALUES (1, XMLTYPE(
  EMPTY_CLOB()
  || 'first 4000 bytes...'
  || 'second 4000 bytes...'
  || 'etc.'
));

In PL/SQL, the limit for strings is 32,000 bytes:

DECLARE
  v_id  NUMBER := 1;
  v_xml VARCHAR2(32000) := 'your 32k XML string';
BEGIN
  INSERT INTO foo(id, document) VALUES (v_id, XMLTYPE(v_xml));
END;
/

Otherwise, you can use the same technique as the SQL answer in PL/SQL:

DECLARE
  v_id  NUMBER := 1;
  v_xml CLOB := EMPTY_CLOB()
                || 'first 32k XML string'
                || 'second 32k XML string'
                || 'etc.';
BEGIN
  INSERT INTO foo(id, document) VALUES (v_id, XMLTYPE(v_xml));
END;
/

CodePudding user response：

Change column type to LONG as it is used to store html, xml data.