Can the Oracle PHP function oci_bind_array_by_name() handle nulls? - Stack Overflow

admin2025-05-01 1

When I use the Oracle PHP function oci_bind_array_by_name(), it converts all nulls in my array to 0s for any number fields and 0-length strings for varchar2s. The official documentation does not mention this behavior. Is it a bug? Is it intended? If intended, is there some setting or parameter I can change to keep nulls as nulls?

Here is a short example:

<?php

$db = oci_pconnect($userId, $password, $connectionString);
if (!$db) {
        echo 'Could not connect to DB.<br />';
        die();
}

$sql = 'BEGIN test_proc(:number, :integer, :varchar, :float); END;';
$stmt = oci_parse($db, $sql);
if (!$stmt) {
        print_r(oci_error($db));
        die();
}

$number = [null, 0, 1];
$integer = [null, 0, 1];
$float = [null, 0.0, 1.1];
$varchar = [null, '', 'test'];

echo '<h1>Before using oci_bind_array_by_name</h1>';
echo 'Number array:';
var_dump($number);
echo 'Integer array:';
var_dump($integer);
echo 'Float array:';
var_dump($float);
echo 'VarChar2 array:';
var_dump($varchar);

$bind = oci_bind_array_by_name($stmt, ':number', $number, count($number), -1, SQLT_NUM);
$bind = oci_bind_array_by_name($stmt, ':integer', $integer, count($integer), -1, SQLT_INT);
$bind = oci_bind_array_by_name($stmt, ':float', $float, count($float), -1, SQLT_FLT);
$bind = oci_bind_array_by_name($stmt, ':varchar', $varchar, count($varchar), -1, SQLT_CHR);

echo '<h1>After using oci_bind_array_by_name</h1>';
echo 'Number array:';
var_dump($number);
echo 'Integer array:';
var_dump($integer);
echo 'Float array:';
var_dump($float);
echo 'VarChar2 array:';
var_dump($varchar);

The output is:

Before using oci_bind_array_by_name
Number array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:23:
array (size=3)
  0 => null
  1 => int 0
  2 => int 1

Integer array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:25:
array (size=3)
  0 => null
  1 => int 0
  2 => int 1

Float array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:27:
array (size=3)
  0 => null
  1 => float 0
  2 => float 1.1

VarChar2 array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:29:
array (size=3)
  0 => null
  1 => string '' (length=0)
  2 => string 'test' (length=4)

After using oci_bind_array_by_name
Number array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:38:
array (size=3)
  0 => int 0
  1 => int 0
  2 => int 1

Integer array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:40:
array (size=3)
  0 => int 0
  1 => int 0
  2 => int 1

Float array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:42:
array (size=3)
  0 => float 0
  1 => float 0
  2 => float 1.1

VarChar2 array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:44:
array (size=3)
  0 => string '' (length=0)
  1 => string '' (length=0)
  2 => string 'test' (length=4)

Here is a short example:

<?php

$db = oci_pconnect($userId, $password, $connectionString);
if (!$db) {
        echo 'Could not connect to DB.<br />';
        die();
}

$sql = 'BEGIN test_proc(:number, :integer, :varchar, :float); END;';
$stmt = oci_parse($db, $sql);
if (!$stmt) {
        print_r(oci_error($db));
        die();
}

$number = [null, 0, 1];
$integer = [null, 0, 1];
$float = [null, 0.0, 1.1];
$varchar = [null, '', 'test'];

echo '<h1>Before using oci_bind_array_by_name</h1>';
echo 'Number array:';
var_dump($number);
echo 'Integer array:';
var_dump($integer);
echo 'Float array:';
var_dump($float);
echo 'VarChar2 array:';
var_dump($varchar);

$bind = oci_bind_array_by_name($stmt, ':number', $number, count($number), -1, SQLT_NUM);
$bind = oci_bind_array_by_name($stmt, ':integer', $integer, count($integer), -1, SQLT_INT);
$bind = oci_bind_array_by_name($stmt, ':float', $float, count($float), -1, SQLT_FLT);
$bind = oci_bind_array_by_name($stmt, ':varchar', $varchar, count($varchar), -1, SQLT_CHR);

echo '<h1>After using oci_bind_array_by_name</h1>';
echo 'Number array:';
var_dump($number);
echo 'Integer array:';
var_dump($integer);
echo 'Float array:';
var_dump($float);
echo 'VarChar2 array:';
var_dump($varchar);

The output is:

Before using oci_bind_array_by_name
Number array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:23:
array (size=3)
  0 => null
  1 => int 0
  2 => int 1

Integer array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:25:
array (size=3)
  0 => null
  1 => int 0
  2 => int 1

Float array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:27:
array (size=3)
  0 => null
  1 => float 0
  2 => float 1.1

VarChar2 array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:29:
array (size=3)
  0 => null
  1 => string '' (length=0)
  2 => string 'test' (length=4)

After using oci_bind_array_by_name
Number array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:38:
array (size=3)
  0 => int 0
  1 => int 0
  2 => int 1

Integer array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:40:
array (size=3)
  0 => int 0
  1 => int 0
  2 => int 1

Float array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:42:
array (size=3)
  0 => float 0
  1 => float 0
  2 => float 1.1

VarChar2 array:

/home/toomanycooks/public_html/bulk_insert_nulls.php:44:
array (size=3)
  0 => string '' (length=0)
  1 => string '' (length=0)
  2 => string 'test' (length=4)

Share Improve this question edited Jan 3 at 7:58 Olivier 18.5k1 gold badge11 silver badges31 bronze badges asked Jan 2 at 14:50 TooManyCooks 336 bronze badges

The fact that the 3rd argument is a reference is an indication that the contents of the array can change. The comment "Whether it will be used for input or output" indicates that, in output mode, the values will overwritten by whatever is output. The docs for oci_bind_by_name() has a few additional details about output binding. "Oracle will convert the data between this type and the database column (or PL/SQL variable type), when possible." – Alex Howansky Commented Jan 2 at 15:46
Regarding how to keep nulls, not sure, and hard to say without understanding your use case. Maybe use empty() to post-process and convert back? Or keep a copy of the array and compare the two afterwards? – Alex Howansky Commented Jan 2 at 15:50
I wonder -- this example code from the doc page doesn't actually query anything -- does it still overwrite with a non-null in the case where it's actually executing a query and outputting a null? – Alex Howansky Commented Jan 2 at 15:53
@AlexHowansky All of what you say is correct, but the Oracle data types number, int, float, and varchar2 are all allowed to be null, so no conversion should be required. Modifying the array after binding does not affect the values that end up getting passed to Oracle when running $db->execute(), they still end up as 0 or empty strings. My use case is that I'm trying to do bulk inserts into an Oracle DB from PHP, but my null fields are being converted to 0s and empty strings. There are messy workarounds, but I don't want to use them until I've verified they're necessary. – TooManyCooks Commented Jan 2 at 17:46
1 I'm curious if oci_bind_by_name does this, too. Are you able to test that? This note is curious, but I don't know if it is related or not. – Chris Haas Commented Jan 2 at 20:40

| Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

After reviewing the oci source code, it looks like there is no way to use nulls with oci_bind_array_by_name.

转载请注明原文地址:http://www.anycun.com/QandA/1746112107a91836.html

Can the Oracle PHP function oci_bind_array_by_name() handle nulls? - Stack Overflow

1 Answer 1

Can the Oracle PHP function ocibindarraybyname() handle nullsStack Overflow