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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: deeky666 at googlemail dot com
New email:
PHP Version: OS:

 

 [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

Pull Requests

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 Dec 03 17:01:29 2024 UTC