PHP-OCI Call Stored Procedure with parameters

Question

Stored Procedure:

parentchildhrs number(7,0);
childhours number(7,0);

begin

COMBINED_HOURS(122,parentchildhrs,childhours);

end;

The above when executed via Oracle SQL Developer outputs the correct expected value which are hours. Output below

CHILD HOURS 50
parent task id 122
parentchild HOURS 100

Now when I call with PHP-OCI, it outputs nothing.

$taskID = 122;
$parent_hours = '';
$child_hours = '';
$procedure = "BEGIN TASK_COMBINED_CHILD_HRS(:task_id, :parent_child_hours, :child_hours); END;";
$test = $taskmaster->getHours($procedure, [':task_id' => $taskID,':parent_child_hours' => (int)$sum_parent_child_hours,':child_hours' => (int)$sum_child_hours]);



    public function getHours($query, $params){
           $result_hours = parent::ocibindbyname($query, $params);
           return $result_hours;
    }

public static function customquery($query, $params)
{

  try{  

      $stmt = oci_parse($conn, $query);
      foreach ($params as $key => &$value) {              
        oci_bind_by_name($stmt, $key, $value, 32);
      }
      oci_execute($stmt,OCI_COMMIT_ON_SUCCESS);
      oci_commit($conn);
      oci_free_statement($stmt);
      return $params;
      }catch (Exception  $e){
        print_r($e);
    }               
}

Printing the result gives

Array
(
    [:task_id] => 1142
    [:parent_child_hours] => 100
    [:child_hours] => 50
)

How can I bind a PHP variable for parent_child_hours and child_hours as output from a stored procedure variable ?

binding pl/sql output variables is also done with oci_bind_by_name. See example 10. — winkbrace
– winkbrace, Commented Mar 12, 2015 at 13:43
@winkbrace binding is working, but how can I get the output of those variables ? — Slimshadddyyy
– Slimshadddyyy, Commented Mar 12, 2015 at 14:12

Community · Accepted Answer · 2017-05-23 11:43:22Z

0

Using the foreach approach from my answer to another of your questions is not a good idea when dealing with stored procedures.

While it does work (as shown in your question, the variables are set in the $params array after execution) the biggest issue is that you must provide the fourth parameter (maxlength) to oci_bind_by_name. You've used a static value of 32 in your code, but an error is raised when the length of a value exceeds this. It can't be calculated at runtime and setting it to a very large value is inefficient (maybe this isn't a problem for your application).

Since you are running a known stored procedure, you should know the maxlength of output values at design-time and these can statically entered by putting all the oci_* functions in getHours(), instead of trying to abstract all calls to customExecute().

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Mar 12, 2015 at 15:31

timclutton

13.1k3 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP-OCI Call Stored Procedure with parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related