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.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

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.