Skip to main content

Development

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.

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.

Thoughts on “Insert a long XML string to DB table with PL/SQL”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow Us