Accessing CLOBs via PHP
Code to update the value of a CLOB column in a table. The following code selects a lob locator into $lob then saves the "COMMENTS" value from the $value array to it.
$c = oci_connect(DB_USER, DB_PASS, DB_HOST.'/'.DB_NAME); $statement = oci_parse ($c, "update agreement_actions set comments = EMPTY_CLOB() where action_id = :var2 returning comments into :var1"); $lob = oci_new_descriptor($c, OCI_D_LOB); oci_bind_by_name($statement, ':var2', OCI8PreparedStatementVars::getVar($prepare, 1)); oci_bind_by_name($statement, ':var1', $lob, -1, OCI_B_CLOB); oci_execute($statement, OCI_DEFAULT); // use OCI_DEFAULT so $lob->save() works $lob->save($value["COMMENTS"]); oci_commit($c); $lob->close();
Code to query a CLOB column:
$query = "select ACTION_ID, AGREEMENT_ID, ACTION_DATE, ACTION_TYPE, RECOVERABLE_YN, ACTION_COST, COMMENTS CHECKSUM, ACTION_TYPE_NAME from AGREEMENT_ACTIONS where AGREEMENT_ID = :var1 order by ACTION_DATE desc"; $c = oci_connect(DB_USER, DB_PASS, DB_HOST.'/'.DB_NAME); $stmt = oci_parse ($c, $query); oci_bind_by_name($stmt, ':var1', $agreement_id); oci_execute($stmt, OCI_DEFAULT); while (($r = oci_fetch_array($stmt, OCI_ASSOC))) { $result[$r["ACTION_ID"]] = array( "ACTION_ID" => $r["ACTION_ID"] , "AGREEMENT_ID" => $r["AGREEMENT_ID"] , "ACTION_DATE" => $r["ACTION_DATE"] , "ACTION_TYPE" => $r["ACTION_TYPE"] , "COMMENTS" => $r["COMMENTS"]->load() , "RECOVERABLE_YN" => $r["RECOVERABLE_YN"] , "ACTION_COST" => $r["ACTION_COST"] , "CHECKSUM" => $r["CHECKSUM"] , "ACTION_TYPE_NAME" => $r["ACTION_TYPE_NAME"]); $r["COMMENTS"]->free(); // cleanup before next fetch } return $result;