|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #46994 CLOB size does not update when using CLOB IN OUT param in stored procedure
Submitted: 2009-01-02 16:28 UTC Modified: 2009-03-11 16:49 UTC
Avg. Score:5.0 ± 0.0
Reproduced:3 of 3 (100.0%)
Same Version:3 (100.0%)
Same OS:1 (33.3%)
From: n dot bier at icarnegie dot com Assigned:
Status: Closed Package: OCI8 related
PHP Version: 5.2.8 OS: CentOS 5
Private report: No CVE-ID:
 [2009-01-02 16:28 UTC] n dot bier at icarnegie dot com
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:

$db = oci_connect('USERNAME', 'PASSWD', 'SID');
if ($db === false) {
    echo "Cannot connect: " . oci_error() . "\n";

$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";

$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";

echo "\$resultVar is now: " . $resultVar . "|EOL\n";
echo "\$outTextVar is now: " . $outTextVar . "|EOL\n";


echo "\$clobVar is now: " . $clobVar->size() . ": " . $clobVar->load()
	. "|EOL\n";

echo "\n\n";



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


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2009-03-11 16:49 UTC]
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
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
PHP Copyright © 2001-2017 The PHP Group
All rights reserved.
Last updated: Fri Jan 20 07:01:47 2017 UTC