From: n dot bier at icarnegie dot com
Operating system: CentOS 5
PHP version: 5.2.8
PHP Bug Type: OCI8 related
Bug description: CLOB size does not update when using CLOB IN OUT param in
stored procedure
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 bug report at http://bugs.php.net/?id=46994&edit=1
--
Try a CVS snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=46994&r=trysnapshot52
Try a CVS snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=46994&r=trysnapshot53
Try a CVS snapshot (PHP 6.0):
http://bugs.php.net/fix.php?id=46994&r=trysnapshot60
Fixed in CVS:
http://bugs.php.net/fix.php?id=46994&r=fixedcvs
Fixed in CVS and need be documented:
http://bugs.php.net/fix.php?id=46994&r=needdocs
Fixed in release:
http://bugs.php.net/fix.php?id=46994&r=alreadyfixed
Need backtrace:
http://bugs.php.net/fix.php?id=46994&r=needtrace
Need Reproduce Script:
http://bugs.php.net/fix.php?id=46994&r=needscript
Try newer version:
http://bugs.php.net/fix.php?id=46994&r=oldversion
Not developer issue:
http://bugs.php.net/fix.php?id=46994&r=support
Expected behavior:
http://bugs.php.net/fix.php?id=46994&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=46994&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=46994&r=submittedtwice
register_globals:
http://bugs.php.net/fix.php?id=46994&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=46994&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=46994&r=dst
IIS Stability:
http://bugs.php.net/fix.php?id=46994&r=isapi
Install GNU Sed:
http://bugs.php.net/fix.php?id=46994&r=gnused
Floating point limitations:
http://bugs.php.net/fix.php?id=46994&r=float
No Zend Extensions:
http://bugs.php.net/fix.php?id=46994&r=nozend
MySQL Configuration Error:
http://bugs.php.net/fix.php?id=46994&r=mysqlcfg
