Oracle ORA-01460 ：在/var/www/html/rest/res/resolve/Resolence.php中请求的未实现或不合理的转换

当我通过oracle绑定变量传递硬编码值$PY_NUM_ARR和$MBER_NAME以执行存储函数时，它工作正常，我得到了结果。但是当我从数组中传递相同的值时，我得到了ORA错误。我发现很难理解ORA错误，以及为什么它是造成的。在where子句中使用的列的数据类型为： Product_NUM和MEMERM_NAME , php变量类型为“String”，长度约为13个字符或更少。如何消除此错误？我使用的是Oracle 11.2、OCI 8、PHP 5.1.6

--MN_CAT_MAP.PY_NUM VARCHAR2(100)

--MN_CAT_MAP.MBER_ID号(20)

--MN_MMBBERER.MBERER_ID非空数字(20)

--MN_MMBBERER.MBERER_NAME非空VARCHAR2(100)

public function resolvedPrice($arr_http_data){
   $PRODUCT_NUM_ARR=array('130342','270179'); //this works 
   $MEMBER_NAME='87307-3'; //this works 
   $EFFECTIVE_DATE='2016-12-01';//this works
   //$PRODUCT_NUM_ARR=$arr_http_data['productNumbers']; //This does not work where arr_http_data has an array with same values as the one above which works
   //$MEMBER_NAME=$arr_http_data['customerNumber']; //This does not work where arr_http_data['customerNumber'] has same value as the one above which works
   //$EFFECTIVE_DATE=$arr_http_data['pricingDate']; //does not work
  foreach($PRODUCT_NUM_ARR as $PRODUCT_NUM){
   $sql_proc = "
   DECLARE
            v_MEMBER_ID NUMBER;
            v_PRODUCT_ID NUMBER;
            v_PMLI_PK NUMBER;

   BEGIN
            SELECT cat_map_id INTO v_PRODUCT_ID
            FROM mn_cat_map WHERE product_num = :PRODUCT_NUM and catalog_type = 'INT';

            SELECT member_id INTO v_MEMBER_ID
            FROM mn_member WHERE member_name = :MEMBER_NAME;

            v_PMLI_PK := pkg_name.function_name(:CONFIG_NAME,:BUS_SEG_CODE,v_MEMBER_ID,v_PRODUCT_ID,
                         TO_TIMESTAMP(TO_DATE(:EFFECTIVE_DATE,'YYYY-MM-DD')),
                         TO_TIMESTAMP(TO_DATE(:MODEL_DATE,'YYYY-MM-DD')),
                         :CURRENCY_CODE,:ORG_UNIT_ID,:RESOLVED_PRICE,:RESOLVED_CURRENCY,:COMMITMENT_ID,:RESOLVED_BASE_PRICE,:RESOLVED_DISCOUNT,
                         :RESOLVED_DISCOUNT_TYPE,:RESOLVED_TIER_INDEX,:CONTRACT_ID_NUM,:PRODUCT_GROUP_ID);

   EXCEPTION
        WHEN no_data_found THEN
          dbms_output.put_line('No Prices Found for these data!');
        WHEN others THEN
          dbms_output.put_line('Error!');
   END;
   ";

   $stmt = oci_parse($conn,$sql_proc);
   oci_bind_by_name($stmt,':PRODUCT_NUM',$PRODUCT_NUM,4000,SQLT_CHR);
   oci_bind_by_name($stmt,':MEMBER_NAME',$MEMBER_NAME,4000,SQLT_CHR);
   oci_bind_by_name($stmt,':EFFECTIVE_DATE',$EFFECTIVE_DATE);
   //not sharing the other bind variables that i am passing as it will become too long.

   $result=oci_execute($stmt); //error occurs here when i pass values from the array .this is line 96

   if (!$result){
        $e = oci_error($stmt);  // For oci_execute errors pass the statement handle
        echo 'Caught exception: '.$e."\n";
        print htmlentities($e['message']);
        print "\n
\n";
        print htmlentities($e['sqltext']);
        printf("\n%".($e['offset']+1)."s", "^");
        print  "\n
\n";
        break;
    }
    else {
        $json_response_arr = array("productNumber" => $PRODUCT_NUM,"basePrice" => $RESOLVED_BASE_PRICE,"resolvedPrice" => $RESOLVED_PRICE,"upChargeAmount" => null,"currency" => $RESOLVED_CURRENCY,"pricingDocType" => null,"pricingDocName" => null,"tierName" => "Tier ".$RESOLVED_TIER_INDEX,"errorMessage" => null,"pricingDocId" => $CONTRACT_ID_NUM,"discount" => $RESOLVED_DISCOUNT);

        $resolve_price_arr += array($i => $json_response_arr);

      //  print_r($json_response_arr);
       // echo json_encode($resolve_price_arr,JSON_PRETTY_PRINT);
    }
  $i = $i + 1;
}// end for each loop

    $json_response_arr=array("resolvedPrices" => $resolve_price_arr);
    echo json_encode($json_response_arr,JSON_PRETTY_PRINT)."\n\n";
    //print_r($json_response_arr);
    return $json_response_arr;
    echo "

"; oci_free_statement($stmt); oci_close($conn); } else { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR); } }// end function resolvePrice() }//end Class ResolvePrice This is the data that i am passing from the Chrome REST Client { "customerNumber": "111003_CUST", "productNumbers": ["1000184", "11100300100"], "pricingDate": "2016-12-01", "currency": "USD", "org": "Root" } // i get the json string from the REST client as follows $jsondata = file_get_contents("php://input"); echo "Raw JSON Data below"."\n".$jsondata; // i decode the json into array and pass it to the resolvedPrice $json_arr = json_decode($jsondata,true); echo "Decoded Json Array is "."\n"; print_r($json_arr);

我不太懂PHP ，但是ora-1460是当您尝试绑定超出数据类型允许大小的内容时，例如：

SQL> declare
  2    s1  varchar2(4001) := rpad(' ',4001);   -- exceeds the 4k bind limit for varchar2
  3    s2  varchar2(4000);
  4  begin
  5    select s1 into s2 from dual;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5

所以推测在这里的某个地方，我们得到的值比4k大。也许是尾随空间？

一旦这些值被捕获到数组中，你能把它们打印出来吗？

如果这还没有帮助，我认为你需要把这个放到PHP论坛上

https://community.oracle.com/community/database/developer-tools/php

其他方面也欢迎分享他们的意见。