Bug #72524 [Opn->Csd]: Binding null values triggers ORA-24816 error

Edit report at https://bugs.php.net/bug.php?id=72524&edit=1 ID: 72524 Updated by: sixd@php.net Reported by: deeky666 at googlemail dot com Summary: Binding null values triggers ORA-24816 error -Status: Open +Status: Closed Type: Bug Package: OCI8 related Operating System: Ubuntu 15.10 PHP Version: 7.0.8 Block user comment: N Private report: N New Comment: 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) Previous Comments: ------------------------------------------------------------------------ [2016-07-29 09:03:09] 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-07-26 05:41:06] 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-06-30 12:37:42] 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) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=72524&edit=1