The purpose is to copy data contained in XMLTYPE type column residing in a local table into a LONG type column in a remote table. Our current PLSQL (package) code first selects XMLTYPE column into a XMLTYPE variable (lv_xml_doc), converts into CLOB and assigns to a LONG variable (lv_xml_long) before inserting into the remote table. A representative piece of code will look like:

DECLARE
........
lv_xml_doc XMLTYPE;
lv_xml_clob CLOB;
lv_xml_long LONG;
BEGIN
SELECT My_XML_Type_Column
INTO lv_xml_doc
FROM My_Local_Table;
lv_xml_clob := lv_xml_doc.getClobVal();
lv_xml_long := lv_xml_clob; --this fails over 32K
INSERT INTO Target_Table_With_LONG (LONG_Type_Column)
VALUES (lv_xml_long);
..........
END;

This code works without issues when the size of lv_xml_clob is less than 32K. If higher, "lv_xml_long := lv_xml_clob" statement will error out as lv_xml_long as a LONG PLSQL variable cannot be greater than 32K.

In essence, we can't seem to find a way to transfer large data (over 32K, as converted to CLOB) into a LONG type field. I'm aware of limitations around dealing with LONG type and Oracle's recommendation to convert to LOB but the remote system is a third party we interface with and unfortunately out of our reach. Is there a solution in PLSQL or SQL realm?