Streaming large data (LOBs) directly into an Oracle database with PHP

The OCI library allows you to stream large data in chunks directly into a LOB instead of loading it completely into memory first. This technique is only mentioned in the article Working with LOBs in Oracle and PHP on the Oracle Technology Network, but no code example is given there, so here’s my take:

$sql = "INSERT INTO myTable (myUploadBinary)
  VALUES (EMPTY_BLOB())
  RETURNING myUploadBinary INTO :myBinary";
$stmt = oci_parse($conn, $sql);
$lob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ":myBinary", $lob, -1, OCI_B_BLOB);
$this->execute($stmt);

$handle = fopen("myFile", 'r');
while (($buffer = fread($handle, 2048)) != '') {
  $lob->write($buffer);
  $lob->flush();
  flush();
  ob_flush();
}
fclose($handle);
oci_commit();
oci_free_statement($stmt);
$lob->free();

Leave a Reply

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