PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Mon Mar 27, 2017 11:36 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 
Offline
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

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

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
  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 = <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,
                         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  <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";
        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"
}

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";
    print_r($json_arr);
 


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

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

but you're passing it strings
Code:
   oci_bind_by_name($stmt,':PRODUCT_NUM',$PRODUCT_NUM,4000,SQLT_CHR);
   oci_bind_by_name($stmt,':MEMBER_NAME',$MEMBER_NAME,4000,SQLT_CHR);


Top
 Profile  
 
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 5 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