Insert a long XML string to DB table with PL/SQL

You want to insert an XML payload record in DB table, but the content for that column is too long and you can’t insert it into table. The traditional SQL: “insert into table (field1) values (‘yourxml’)” will fail because of the long characters. How do you handle this situation?

Scenario:  client requires to insert a XSLT/XQuery into one of the column in Oracle DB table, but the length of the XSLT is too long, oracle DB will  throw an exception if try to insert it into DB.

The exception error is like: “SQL Error: ORA-01704: string literal too long; Cause:    The string literal is longer than 4000 characters.”

Solution: We can use PL/SQL blocks and XML type data type to resolve this problem.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

The format is just like this:

Declare yourXmlStr xmltype: =xmltype(‘your xml’);

Begin

Insert into table (column) values (yourXmlStr);

End;

Run this block and commit it, the record should be inserted into DB successfully.

PS: The required column of DB table should have no character size limited, you can defined the type of that column “clob”, of course PL/Sql support many data type, here just set the XML type as an example.