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

Reply

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();

PHP Tip: Binding variables in a SQL WHERE IN clause

3 Replies

Have you ever wondered if it is possible to bind an unknown amount of variables in a SQL WHERE IN clause, e.g.:

SELECT id, text FROM myTable WHERE id IN (:myId1, :myId2, myId3, ...)

Use a OCI collection object and wonder no more:

$keyList = array(100, 250, 350);
$stmt = oci_parse($cnn, "SELECT id, text FROM myTable WHERE id IN (SELECT column_value from table(:myIds))");
$coll = oci_new_collection($cnn, 'ODCIVARCHAR2LIST','SYS');
foreach ($keyList as $key) {
   $coll->append($key);
}
oci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);
oci_execute($stmt);

while($row = oci_fetch_array($stmt, OCI_ASSOC)) {
    echo "{$row['ID']}, {$row['TEXT']}";
}

I found the above solution by ThinkJet on stackoverflow.

SQL tip: How to bind NULL with PHP and Oracle

Reply

Neat little trick to bind a null value without using an if clause.

How often have you done something like this when a variable might be null:

$sql = "SELECT * FROM someTable WHERE col1 = :firstVar";
if (isset($secondVar)) {
   $sql.= " AND col2 = :secondVar";
}
...
oci_bind_by_name($stmt, ':firstVar', $firstVar);
if (isset($secondVar)) {
   oci_bind_by_name($stmt, ':secondVar', $secondVar);
}

Then checkout this, which is quite a bit shorter and a lot easier to read:

$sql = "SELECT * FROM someTable WHERE col1 = :firstVar
   AND (col2 = :secondVar OR :secondVar IS NULL)";
...
oci_bind_by_name($stmt, ':firstVar', $firstVar);
oci_bind_by_name($stmt, ':secondVar', $secondVar);