By cj on Feb 11, 2014
I've released PHP OCI8 2.0.7 which has oci_bind_by_name() support for binding PL/SQL's BOOLEAN type to PHP's boolean. The feature is available when PHP OCI8 2.0.7 is linked-with and connects-to Oracle Database 12c. (The necessary Oracle C library support for binding BOOLEAN was introduced in 12c).
Following the existing PHP OCI8 convention, there are two new constants usable with oci_bind_by_name(): SQLT_BOL and OCI_B_BOL. They have identical values. The former mirrors the underlying Oracle library constant's name (yes, it only has one "O"). The latter follows the PHP OCI8 style of having OCI_B_-prefixed names for bind constants. Note the constants can't be used for array binding with oci_bind_array_by_name().
An example usng the new PHP OCI8 2.0.7 feature is:
<?php /* Precreate this PL/SQL function: create or replace function is_valid(p in number) return boolean as begin if (p < 10) then return true; else return false; end if; end; / */ $c = oci_connect('hr', 'welcome', 'localhost/pdborcl'); $sql = "begin :r := is_valid(40); end;"; $s = oci_parse($c, $sql); oci_bind_by_name($s, ':r', $r, -1, SQLT_BOL); // no need to give length oci_execute($s); var_dump($r); // Outputs: bool(false) ?>
Prior to OCI8 2.0.7 you had to write a wrapper PL/SQL block that mapped the PL/SQL true or false values to 1 or 0.
Code without using OCI8 2.0.7:
$sql = "begin if (is_valid(40) = true) then :r := 1; else :r := 0; end if; end;"; $s = oci_parse($c, $sql); oci_bind_by_name($s, ':r', $r, -1, SQLT_INT); oci_execute($s); echo "Result is " . ($r ? "true" : "false") . "\n"; // Outputs: Result is false
The new functionality removes one small pain point and makes your interaction with Oracle Database 12c PL/SQL cleaner and easier.