Bug #2807: LOB descriptors with Fetch/Result and FetchInto

From: robert.everett@wcom.com Operating system: Solaris 2.6 PHP version: 3.0.12 PHP Bug Type: Oracle related Bug description: LOB descriptors with Fetch/Result and FetchInto 1. When selecting a row containing more than one LOB, the descriptors for all but the first LOB are freed after the fetches are complete. 2. When selecting multiple rows containing a LOB, descriptors are only created for one row and reused. Therefore, the descriptors are only valid for the last row after the fetches are complete. In addition, if each row contains more than one LOB, the above condition also applies. This becomes a problem when trying to create an array of the result set for later user since all but one of the LOB descriptors are no longer valid. LOB descriptors must be used *immediately* when fetching a row or they can't be used at all. Unless using the OCI_RETURN_LOBS flag, LOB descriptors should be created for each row and shouldn't be freed until explicitly freed by the user. At the very least, a flag should be created that will allow this behavior (OCI_RETURN_ALL_LOCATORS?). Here are the details: PHP 3.0.12 Apache 1.3.9.2 Oracle 8.0.4 Solaris 2.6 ---- Fetch/Result ---- Code: putenv("ORACLE_HOME=/oracle/home"); putenv("ORACLE_SID=sid"); OCIInternalDebug(1); $conn = OCINLogon("test", "pass", "sid"); $sql = "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)"; $stmt = OCIParse($conn, $sql); OCIExecute($stmt); $rows = 0; $cols = OCINumCols($stmt); while (OCIFetch($stmt)) { for ($i = 1; $i < $cols + 1; $i++) { $colname = OCIColumnName($stmt, $i); $select[$colname][$rows] = OCIResult($stmt, $i); } $rows++; } for ($i = 0; $i < $rows; $i++) { echo "ID: ".$select["ID"][$i]."<br>\n"; echo "Clob: ".$select["CLOB"][$i]->load()."<br>\n"; // always value for last row; only one descriptor created echo "Blob: ".$select["BLOB"][$i]->load()."<br>\n"; // fails; descriptor already freed } OCIFreeStatement($stmt); OCILogoff($conn); Output (debug statements/warnings prefixed with *): *OCIDebug: oci8_open_server new conn=2000 dname=sid *OCIDebug: oci8_open_user new sess=1000 user=test *OCIDebug: oci8_do_connect: id=1 *OCIDebug: oci8_parse "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)" id=2 conn=1 *OCIDebug: OCIExecute: new descriptor for CLOB *OCIDebug: OCIExecute: new descriptor for BLOB *OCIDebug: oci8_free_descr: 3c5498 ID: 1 *OCIDebug: OCIloaddesc: size=14 Clob: blah blah blah *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx Blob: ID: 2 *OCIDebug: OCIloaddesc: size=14 Clob: blah blah blah *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx Blob: *OCIDebug: _oci8_free_stmt: id=2 last_query="SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)" *OCIDebug: _oci8_close_conn: id=1 *OCIDebug: oci8_free_descr: 3c5508 ---- FetchInto ---- Code: putenv("ORACLE_HOME=/oracle/home"); putenv("ORACLE_SID=sid"); OCIInternalDebug(1); $conn = OCINLogon("test", "pass", "sid"); $sql = "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)"; $stmt = OCIParse($conn, $sql); OCIExecute($stmt); $rows = 0; while (OCIFetchInto($stmt, $select[$rows], OCI_ASSOC)) { $rows++; } for ($i = 0; $i < $rows; $i++) { echo "ID: ".$select[$i]["ID"]."<br>\n"; echo "Clob: ".$select[$i]["CLOB"]->load()."<br>\n"; // always value for last row; only one descriptor created echo "Blob: ".$select[$i]["BLOB"]->load()."<br>\n"; // fails; descriptor already freed } OCIFreeStatement($stmt); OCILogoff($conn); Output (debug statements/warnings prefixed with *): *OCIDebug: oci8_open_server new conn=2000 dname=sid *OCIDebug: oci8_open_user new sess=1000 user=test *OCIDebug: oci8_do_connect: id=1 *OCIDebug: oci8_parse "SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)" id=2 conn=1 *OCIDebug: OCIExecute: new descriptor for CLOB *OCIDebug: OCIExecute: new descriptor for BLOB *OCIDebug: oci8_free_descr: 3e328c ID: 1 *OCIDebug: OCIloaddesc: size=14 Clob: blah blah blah *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx Blob: ID: 2 *OCIDebug: OCIloaddesc: size=14 Clob: blah blah blah *Warning: unable to find my descriptor 1 in /.../test.phtml on line xx Blob: *OCIDebug: _oci8_free_stmt: id=2 last_query="SELECT id, clob, blob FROM foobar WHERE id IN (1, 2)" *OCIDebug: _oci8_close_conn: id=1 *OCIDebug: oci8_free_descr: 3e32fc