ID: 46994 Updated by: s...@php.net Reported By: n dot bier at icarnegie dot com -Status: Open +Status: Closed Bug Type: OCI8 related Operating System: CentOS 5 PHP Version: 5.2.8 New Comment:
This bug has been fixed in CVS. Snapshots of the sources are packaged every three hours; this change will be in the next snapshot. You can grab the snapshot at http://snaps.php.net/. Thank you for the report, and for helping us make PHP better. --------- Fixed in PHP 5.3 and PHP 6. Will also be in a (future) PECL OCI8 1.3.5 release. The patch is http://cvs.php.net/viewvc.cgi/pecl/oci8/oci8_statement.c?r1=1.7.2.14.2.28.2.12&r2=1.7.2.14.2.28.2.13 Previous Comments: ------------------------------------------------------------------------ [2009-01-02 16:28:33] n dot bier at icarnegie dot com Description: ------------ When using stored procedures, a variable bound to an IN OUT CLOB param does not have its size updated appropriately after execution. The value of the variable is updated correctly, but only to the length of the initial variable value. For example, we allocate a CLOB descriptor and initialize to 10 characters (in this case, 0 - 9), then bind and execute. The stored procedure clears the CLOB, then inserts new text into the clob that is longer (for our example below, the 26 characters of the alphabet). Although the php variable is reflecting the change in the CLOB contents (it changes from 0-9 to a-j), it is not reflecting the change in size (since it should be a-z). This seems to be a caching problem; php_oci_lob_get_length() in oci8_lob.c caches the length, and the cache value isn't updated after calling the stored procedure. This can be worked around by modifying php_oci_lob_get_length() to force execution to fetch the LOB's length by avoiding using the number stored in the descriptor (a one line change, see comment): /* {{{ php_oci_lob_get_length() Get length of the LOB. The length is cached so we don't need to ask Oracle every time */ int php_oci_lob_get_length (php_oci_descriptor *descriptor, ub4 *length TSRMLS_DC) { php_oci_connection *connection = descriptor->connection; *length = 0; /*CHANGED HERE*/ if (0 && descriptor->lob_size >= 0) { *length = descriptor->lob_size; return 0; } else { ... Note that this fix may be necessary but might not be sufficient, in that the implementor was trying to be efficient by "caching" the value. The uses of php_oci_lob_get_length() should be reviewed, as some may still be able to use the cached value. Reproduce code: --------------- PHP code is below; source for stored procedure at: http://henry.icarnegie.com/~nbier/testpackage.sql <?php $db = oci_connect('USERNAME', 'PASSWD', 'SID'); if ($db === false) { echo "Cannot connect: " . oci_error() . "\n"; exit; } $storedProcedure = ":result := trestle.TestPackage.TestFunction(:in_text, " . ":out_text, :inout_clob);"; $stmt = oci_parse($db, "BEGIN " . $storedProcedure . " END;"); if ($stmt === false) { echo "Cannot parse: " . oci_error($stmt) . "\n"; exit; } $resultVar = ""; $inTextVar = "Text input to stored procedure"; $outTextVar = ""; oci_bind_by_name($stmt, ":result", $resultVar, 5000); oci_bind_by_name($stmt, ":in_text", $inTextVar, -1); oci_bind_by_name($stmt, ":out_text", $outTextVar, 5000); $clobVar = oci_new_descriptor($db, OCI_D_LOB); $clobVar->writeTemporary("0123456789", OCI_TEMP_CLOB); oci_bind_by_name($stmt, ":inout_clob", $clobVar, -1, SQLT_CLOB); $success = oci_execute($stmt, OCI_DEFAULT); if ($success === false) { echo "Execute failed: " . oci_error($stmt) . "\n"; oci_free_statement($stmt); exit; } echo "\$resultVar is now: " . $resultVar . "|EOL\n"; echo "\$outTextVar is now: " . $outTextVar . "|EOL\n"; $clobVar->rewind(); echo "\$clobVar is now: " . $clobVar->size() . ": " . $clobVar->load() . "|EOL\n"; echo "\n\n"; oci_free_statement($stmt); oci_close($db); ?> Expected result: ---------------- PROMPT> php testclobstoredprocedure.php $resultVar is now: Text input to stored procedure|EOL $outTextVar is now: 10: 0123456789 CLOB then becomes: abcdefghijklmnopqrstuvwxyZ|EOL $clobVar is now: 26: abcdefghijklmnopqrstuvwxyZ|EOL Actual result: -------------- PROMPT> php testclobstoredprocedure.php $resultVar is now: Text input to stored procedure|EOL $outTextVar is now: 10: 0123456789 CLOB then becomes: abcdefghijklmnopqrstuvwxyZ|EOL $clobVar is now: 10: abcdefghij|EOL ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=46994&edit=1
