从PHP调用for循环中存储的oracle过程仅适用于最后一次迭代

Question

I have a stored procedure in php to treat some data. Here is how i call it:

$listeDeNdi= $_POST['LISTEDENDI'];
    $ndiTable=split("
", $listeDeNdi);
    $calibreLongueur= $_POST['CALIBRELONGUEUR'];
    $validit=$_POST['VALIDITE'];

$sql="DECLARE
              PI_NDI NUMBER;
              PI_VALIDITE NUMBER;
              PI_CALIBRE_LONGUEUR VARCHAR2(32767);
              PO_CODE_ERR NUMBER;
              PO_LIB_ERR VARCHAR2(32767);

              BEGIN
              ARIANE_ADMIN.P_FORCE_ELIGIBILITE ( :PI_NDI, :PI_VALIDITE, :PI_CALIBRE_LONGUEUR, :PO_CODE_ERR, :PO_LIB_ERR );
              END;";

$stid = oci_parse($conn, $sql);
for ( $i=0;$i<count($ndiTable); $i++){
    $binds = [
    ':PI_VALIDITE'=> $validit,
    ':PI_CALIBRE_LONGUEUR'=> $calibreLongueur,
    ':PI_NDI'=> $ndiTable[$i],
    ':PO_CODE_ERR'=> $PO_CODE_ERR,
    ':PO_LIB_ERR'=> $PO_LIB_ERR,
    ];

    foreach ($binds as $key => $val){
      if($key === ':PO_CODE_ERR'){
       oci_bind_by_name($stid, $key, $binds[$key],40);
       } else if ($key === ':PO_LIB_ERR'){
         oci_bind_by_name($stid, $key, $binds[$key], 32767 , SQLT_CHR);
         }else {
          oci_bind_by_name($stid, $key, $binds[$key]);
          }
      }
    oci_execute($stid);
    echo "For NDI ".$ndiTable[$i]." :<br> ";
    echo "PO_LIB_ERR : ".$binds[':PO_LIB_ERR'];
    echo "<br>";
    echo "PO_CODE_ERR : ".$binds[':PO_CODE_ERR'];
    echo "<br>";
    $code[$i]=$binds[':PO_CODE_ERR'];
    $lib[$i]=$binds[':PO_LIB_ERR'];

}
foreach($code as $i){
 echo $code[$i]."<br>";
 echo "hi<br> ";
 }

foreach($lib as $i){
 echo $lib[$i]."<br>";
 echo "hi<br> ";
 }

I am getting a list of phone numbers from a form and i call the procedure once per element of this list, as you can see in the for loop in the above code.

It works fine when the list contains only one element, but when the list contains more than one it seems only the last iteration fills my out parameters PO_CODE_ERR and PO_LIB_ERR.

I have searched and found it might be an issue with the function oci_bind_by_name and loops, as said in the php manual :

---

The PHP variable argument is a reference. Some forms of loops do not work as expected:

<?php
foreach ($myarray as $key => $value)  {
oci_bind_by_name($stid, $key, $value);
}
?>    

This binds each key to the location of $value, so all bound variables end up pointing to the last loop iteration's value. Instead use the following:

<?php
foreach ($myarray as $key => $value) {
oci_bind_by_name($stid, $key, $myarray[$key]);
}
?>    

---

However, i am not sure how it applies to my case and how to solve it. Any help will be greatly appreciated. Many thanks !

---

Edit : adding the prints i didn't specify and the results i got from them :

When there's only one element in the list :

For NDI 987987987 :
PO_LIB_ERR : ERREUR : AUCUNE INFO SUR CE NDI DANS LE CACHE ARIANE
PO_CODE_ERR : 1

When there's more than one element in the list :

For NDI 987987987 :
PO_LIB_ERR :
PO_CODE_ERR :
For NDI 654654654 :
PO_LIB_ERR :
PO_CODE_ERR :
For NDI 321321321 :
PO_LIB_ERR : ERREUR : AUCUNE INFO SUR CE NDI DANS LE CACHE ARIANE
PO_CODE_ERR : 1

---

Edit2 : i tried storing the parameters PO_LIB_ERR and PO_CODE_ERR in two arrays and print them after my for loop (see code above). Althought i can see the results printed in the for loop, i cannot see them in my new arrays, they simply display empty lines when i echo them.

Like this :

For NDI 787778781 :
PO_LIB_ERR : ERREUR : AUCUNE INFO SUR CE NDI DANS LE CACHE ARIANE
PO_CODE_ERR : 1

hi

hi

Answer 1

After some time we found the solution using oci_error to print any error message that would come from oci_execute.

It turns out our phone numbers were strings instead of integers so oracle was returning this error :

numeric or value error: character to number conversion error ORA-06512

we just had to cast our phone number array in the mapping :

$binds = [
    ':PI_VALIDITE'=> $validit,
    ':PI_CALIBRE_LONGUEUR'=> $calibreLongueur,
    ':PI_NDI'=>(int)$ndiTable[$i],
    ':PO_CODE_ERR'=> $PO_CODE_ERR,
    ':PO_LIB_ERR'=> $PO_LIB_ERR
    ];