PHP and Oracle: Christopher Jones

Today a reader mailed me about manipulating XMLType columns when the data is longer than the 4K limit that character-type handling imposes. 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:

<?php

/*
Table definition is:
create table xwarehouses (warehouse_id number, warehouse_spec xmltype);
*/

$c = oci_connect('hr', 'hrpwd', 'localhost/XE');

$id = 1;

// Delete any current entry
$s = oci_parse($c, "delete from xwarehouses where warehouse_id = :id");
oci_bind_by_name($s, ':id', $id);
oci_execute($s);

// XML data to be inserted
$xml =<<<EOF
<?xml version="1.0"?>
<Warehouse>
<WarehouseId>1</WarehouseId>
<WarehouseName>Southlake, Texas</WarehouseName>
<Building>Owned</Building>
<Area>25000</Area>
<Docks>2</Docks>
<DockType>Rear load</DockType>
<WaterAccess>true</WaterAccess>
<RailAccess>N</RailAccess>
<Parking>Street</Parking>
<VClearance>10</VClearance>
</Warehouse>
EOF;

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

// Query the row back
$s = oci_parse($c, 'select xmltype.getclobval(warehouse_spec)
                    from xwarehouses where warehouse_id = :id');
$r = oci_bind_by_name($s, ':id', $id);
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);

// Manipulate the data using SimpleXML
$sx = simplexml_load_string((binary)$row[0]->load());
$row[0]->free();
$sx->Docks -= 1;  // change the data

// Update changes using a temporary CLOB
$s = oci_parse($c, 'update xwarehouses
                    set warehouse_spec = XMLType(:clob)
                    where warehouse_id = :id');
oci_bind_by_name($s, ':id', $id);
$lob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ':clob', $lob, -1, OCI_B_CLOB);
$lob->writeTemporary($sx->asXml());
oci_execute($s);
$lob->close();

// Query the changed row back and print it
$s = oci_parse($c, 'select xmltype.getclobval(warehouse_spec)
                    from xwarehouses where warehouse_id = :id');
$r = oci_bind_by_name($s, ':id', $id);
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);
print htmlentities($row[0]->load());
$row[0]->free();

?>