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 11.2 , 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)
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<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"
}
// 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);
I dont really much PHP skills, but ora-1460 is when you try to bind something that exceeds the allowable size of the datatype, for example:
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
so presumably somewhere in there, we're getting a value which is larger then 4k. Perhaps trailing spaces ?
Can you print out the values once they've been captured into the array and see how long they are ?
If that doesnt help, I'll think you need to post this into the PHP forum
https://community.oracle.com/community/database/developer-tools/php Others welcome to share their input.