ORA-01460: unimplemented or unreasonable conversion requeste

[rsanjay]

when i pass the hardcoded values $PRODUCT_NUM_ARR and $MEMBER_NAME through an oracle bind variable to execute a stored function,it works fine and i get the result. But when i pass the same values from an array i get the ORA error. I have found difficulty in understanding the ORA error and why it is being caused. the datatype of the columns PRODUCT_NUM and MEMBER_NAME used in the where clause are as follows and the php variables types are "String" and of length approx 13 characters or less. How do i get rid of this error ? i am using Oracle 10.1 , OCI 8,PHP 5.1.6

--MN_CAT_MAP.PRODUCT_NUM VARCHAR2(100)

--MN_CAT_MAP.MEMBER_ID NUMBER(20)

--MN_MEMBER.MEMBER_ID NOT NULL NUMBER(20)

--MN_MEMBER.MEMBER_NAME NOT NULL VARCHAR2(100)

Code: Select all

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  :banghead:

   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<pre>\n";
        print htmlentities($e['sqltext']);
        printf("\n%".($e['offset']+1)."s", "^");
        print  "\n</pre>\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 "</pre>";
    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"
}

Code: Select all

    // 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);

[requinix]

The procedure wants numbers

Code: Select all

   DECLARE
            v_MEMBER_ID NUMBER;
            v_PRODUCT_ID NUMBER;
            v_PMLI_PK NUMBER;

but you're passing it strings

Code: Select all

   oci_bind_by_name($stmt,':PRODUCT_NUM',$PRODUCT_NUM,4000,SQLT_CHR);
   oci_bind_by_name($stmt,':MEMBER_NAME',$MEMBER_NAME,4000,SQLT_CHR);