PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
It is currently Sun Mar 18, 2018 4:49 am

All times are UTC - 5 hours

Post new topic Reply to topic  [ 2 posts ] 
Author Message
PostPosted: Sun Dec 11, 2016 3:31 pm 
Forum Newbie

Joined: Mon Jun 07, 2010 9:33 pm
Posts: 2
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





Syntax: [ Download ] [ Hide ]
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
   $sql_proc = "
            v_MEMBER_ID NUMBER;
            v_PRODUCT_ID NUMBER;
            v_PMLI_PK NUMBER;

            SELECT cat_map_id INTO v_PRODUCT_ID
            FROM mn_cat_map WHERE product_num = <img src="
./images/smilies/icon_razz.gif" alt=":P" title="Razz" />RODUCT_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,

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

   $stmt = oci_parse($conn,$sql_proc);
   //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  <img src="./images/smilies/banghead.gif" alt=":banghead:" title="Banging head" />

   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";
    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";
    return $json_response_arr;
    echo "</pre>";
  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"

Syntax: [ Download ] [ Hide ]
    // 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";

PostPosted: Sun Dec 11, 2016 10:25 pm 
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
The procedure wants numbers
            v_MEMBER_ID NUMBER;
            v_PRODUCT_ID NUMBER;
            v_PMLI_PK NUMBER;

but you're passing it strings

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours

Who is online

Users browsing this forum: No registered users and 4 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group