php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #72524 Binding null values triggers ORA-24816 error
Submitted: 2016-06-30 12:37 UTC Modified: 2016-07-26 05:41 UTC
Votes:5
Avg. Score:4.2 ± 1.0
Reproduced:4 of 4 (100.0%)
Same Version:3 (75.0%)
Same OS:0 (0.0%)
From: deeky666 at googlemail dot com Assigned:
Status: Closed Package: OCI8 related
PHP Version: 7.0.8 OS: Ubuntu 15.10
Private report: No CVE-ID: None
 [2016-06-30 12:37 UTC] deeky666 at googlemail dot com
Description:
------------
There seems to be a difference between PHP5 and PHP7 in oci8 when binding NULL values to LONG/LOB type columns.

In PHP5 it was possible to execute the following INSERT statement without any error (sample):

INSERT INTO mytable VALUES (:clob_col, :varchar2_col)

If the value bound to parameter :varchar2_col is NULL, the following error is triggered (no matter what value the parameter :clob_col is bound to):

ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

If you switch the parameter order and both parameter values are bound to NULL, the same error occurs:

INSERT INTO mytable VALUES (:varchar2_col, :clob_col)

So no matter what order, not matter what value is bound to :clob_col, if :varchar2_col is bound to NULL, the error occurs.

If you have only one VARCHAR2 column or two (without a CLOB column) there also is no error.

Test script:
---------------
<?php

$conn = oci_connect('system', 'oracle', '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=xe)))');

$stmt = oci_parse($conn, 'DROP TABLE mytable');
oci_execute($stmt);

$stmt = oci_parse($conn, 'CREATE TABLE mytable ("clob_col" CLOB DEFAULT NULL, "varchar2_col" VARCHAR2(255) DEFAULT NULL)');
oci_execute($stmt);

$stmt = oci_parse($conn, 'INSERT INTO mytable VALUES (:clob_col, :varchar2_col)');

$clob = null;
$varchar2 = null;
oci_bind_by_name($stmt, ':clob_col', $clob);
oci_bind_by_name($stmt, ':varchar2_col', $varchar2);

var_dump(oci_execute($stmt));

Expected result:
----------------
bool(true)

Actual result:
--------------
PHP Warning:  oci_execute(): ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column in /home/deeky/dev/doctrine/dbal/oci8_php7.php on line 14

Warning: oci_execute(): ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column in /home/deeky/dev/doctrine/dbal/oci8_php7.php on line 14
bool(false)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-07-26 05:41 UTC] sixd@php.net
There are a couple of workarounds.  You could try reordering the bind parameters:
  $stmt = oci_parse($conn, 'INSERT INTO mytable ("varchar2_col", "clob_col") VALUES (:varchar2_col, :clob_col)'); 
or giving a size when binding:
  oci_bind_by_name($stmt, ':varchar2_col', $varchar2, 1);
 [2016-07-29 09:03 UTC] soyuka at gmail dot com
Indeed, adding a length works well. Should it be added to the docs? Is there a reason why it worked in php5?
 [2016-08-07 00:03 UTC] sixd@php.net
Automatic comment on behalf of christopher.jones@oracle.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=b601dc5b29147bb1402d78e7f33a90981a2f94f5
Log: Fix bug #72524 (Binding null values triggers ORA-24816 error)
 [2016-08-07 00:03 UTC] sixd@php.net
-Status: Open +Status: Closed
 [2016-08-07 00:04 UTC] sixd@php.net
Automatic comment on behalf of christopher.jones@oracle.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=b601dc5b29147bb1402d78e7f33a90981a2f94f5
Log: Fix bug #72524 (Binding null values triggers ORA-24816 error)
 [2016-08-07 00:04 UTC] sixd@php.net
Automatic comment on behalf of christopher.jones@oracle.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=b601dc5b29147bb1402d78e7f33a90981a2f94f5
Log: Fix bug #72524 (Binding null values triggers ORA-24816 error)
 [2016-08-16 11:19 UTC] peter dot lukan at outlook dot com
I saw that this bugfix has been commited, when it will be available to the "release" for public use?

In case this is not in reasonable time ... I need a suggestion how to install a version of OCI8 with this hotfix included.

Can I clone this repo and build it myself? Or maybe is there a "beta channel releases" available?
 [2016-08-17 06:42 UTC] peter dot lukan at outlook dot com
Got the source here http://git.php.net/?p=php-src.git;a=tree;f=ext/oci8;hb=refs/heads/master

I can confirm that previous functionality with updating clob columns is now working again.
 [2016-10-17 10:10 UTC] bwoebi@php.net
Automatic comment on behalf of christopher.jones@oracle.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=b601dc5b29147bb1402d78e7f33a90981a2f94f5
Log: Fix bug #72524 (Binding null values triggers ORA-24816 error)
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 04:01:31 2024 UTC