Inserting large XML document into Oracle

Question

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.

Does this answer your question? How to insert large XML string into an Oracle Table? — astentx
– astentx, Commented Jul 11, 2022 at 10:23

MT0 · Accepted Answer · 2022-07-11 09:46:17Z

3

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

edited Jul 11, 2022 at 9:46

answered Jul 11, 2022 at 9:38

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Julia Hayward Over a year ago

I tried this, and it produces a script which looks correct but my dockerised Oracle instance segfaults on it... :(

MT0 Over a year ago

@JuliaHayward All the options work in an Oracle 18 database db<>fiddle. Are you using an old database version?

Julia Hayward Over a year ago

Yes, 12.1.0.2. Sadly I'm constrained to that version.

MT0 Over a year ago

@JuliaHayward The final option works in Oracle 11gR2 db<>fiddle. It should work on Oracle 12r1 (but I don't have that version so cannot test it).

Collectives™ on Stack Overflow

Inserting large XML document into Oracle

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related