Inserting and Updating Oracle XMLType columns in PHP

If you have issues with manipulating XMLType columns beyond the 4K limit imposed by character-type handling, Christopher Jones posted code on how to INSERT in Oracle database, since the SELECT and UPDATE are already covered in Underground PHP and Oracle Manual (new manual page).

My free book (see sidebar) has examples of how to do this using CLOB handling in PHP. I noticed that my xmlinsert.php example in the book does a SELECT and UPDATE, but never actually does an INSERT. The INSERT code is conceptually no different from UPDATE but, for completeness, here is an expanded example explicitly showing it

The idea of the code is to use oci_bind_by_name with maxlength=-1 and type=OCI_B_CLOB while you bind value using oci_new_descriptor :

<br /> $xml = ”; // xml data here<br /> // Insert new XML data using a temporary CLOB<br /> $s = oci_parse($c,<br /> “insert into xwarehouses (warehouse_id, warehouse_spec)<br /> values (:id, XMLType(:clob))”);<br /> oci_bind_by_name($s, ‘:id’, $id);<br /> $lob = oci_new_descriptor($c, OCI_D_LOB);<br /> oci_bind_by_name($s, ‘:clob’, $lob, -1, OCI_B_CLOB);<br /> $lob->writeTemporary($xml);<br /> oci_execute($s);<br /> $lob->close();<br />