1 Reply Latest reply: May 13, 2014 10:27 AM by cj RSS

    oci_bind_by_name binds boolean false as NULL




      I'm trying to insert a PHP boolean variable into a column defined as number. It works fine for TRUE, but fails with ORA-01400 for FALSE.

      Reason for using NUMBER(1) is for portability with other DB engines.


      create table test (version varchar(10), obsolete number(1) not null);


      $conn = oci_connect("user", "password", "localhost/XE");
      $sql = "INSERT INTO test (version, obsolete) values (:0, :1)";
      $param = array( 'test', false );
      $stmt=oci_parse($conn, $sql);
      oci_bind_by_name($stmt, ":0", $param[0]);
      oci_bind_by_name($stmt, ":1", $param[1]);


      $ php /tmp/test.php
      PHP Warning:  oci_execute(): ORA-01400: cannot insert NULL into ("USER"."TEST"."OBSOLETE") in /tmp/test.php on line 12


      If I cast to (int) prior to binding the boolean it works so I have an obvious workaround, but my expectation would be that false would be converted to 0, just like true is automatically stored as 1 (with  $param = array( 'test', true );, the execution is successful)


      $ php /tmp/test.php


      SQL> select * from test;

      ---------- ----------
      test                1


      Is this a bug ? If not, can someone explain why this happens and maybe what I'm doing wrong ?






      PHP 5.5.9-1ubuntu4

      OCI8 Version => 2.0.8
      Oracle XE

        • 1. Re: oci_bind_by_name binds boolean false as NULL

          The default type used for oci_bind_by_name is SQLT_CHR, so the boolean value you are passing is being converted internally to a string. In zend_operators.c::_convert_to_string():

            case IS_BOOL:
              if (Z_LVAL_P(op)) {
                Z_STRVAL_P(op) = estrndup_rel("1", 1);
                Z_STRLEN_P(op) = 1;
              } else {
                Z_STRVAL_P(op) = STR_EMPTY_ALLOC();
                Z_STRLEN_P(op) = 0;

          So the 'false' value is converted to an empty string by the second branch.  This leads to the ORA-1400 because of Oracle's traditional handling of empty strings as NULL.

          When you cast to (int) a different conversion path results in the internal representation becoming the string "0".  This is converted to 0 by the underlying Oracle libraries.


          For other readers reference, but not useful in your scenario, OCI8 2.0 (when using Oracle 12c) has support for binding the PL/SQL BOOLEAN type:

            $s = oci_parse($c, "begin :b := true; end;");
            oci_bind_by_name($s, ':b', $b, -1, OCI_B_BOL);
            var_dump($b); // Outputs: bool(true)