How to call an Oracle stored procedure with output parameters using doctrine?

Question

I have been calling stored procs in Oracle using doctrine doing something like:

$sql = "CALL namespace.my_proc(".$data_source_id.", to_date('".$account_period_start."', 'YYYY-MM-DD'),'".$updated_by."')";

$stmt = $this->getDoctrine()->getManager('fdw')->getConnection()->prepare($sql);
            $result = $stmt->execute();
            $stmt->closeCursor();

Now the DBA team has changed one of the stored procs to accept 2 output parameters (x and y) and I am not understanding how to make that happen. Can someone please assist me with that?

Thank You

kratos · Accepted Answer · 2016-01-28 19:30:11Z

3

I was able to find out the info. Hope it helps someone.

$sql = "CALL namespace.my_proc(".$data_source_id.", to_date('".$account_period_start."', 'YYYY-MM-DD'),'".$updated_by."', :x, :y)";
  $stmt = $this->getDoctrine()->getManager('fdw')->getConnection()->prepare($sql);
            $stmt->bindParam(':x', $x, \PDO::PARAM_INPUT_OUTPUT, 32);
            $stmt->bindParam(':y', $y, \PDO::PARAM_INPUT_OUTPUT, 32);
            $result = $stmt->execute();

answered Jan 28, 2016 at 19:30

kratos

2,4952 gold badges30 silver badges49 bronze badges

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

Comments

manix · Accepted Answer · 2021-09-08 22:09:52Z

0

For most recent versions of doctrine:

$sql = 'call my_proc(:arg1, :arg2)';
$stmt = $em->getConnection()->prepare($sql);
$stmt->executeQuery([
    ':arg1' => 82,
    ':arg2' => 'Foo',
]);

However, for output parameters you apply the same logic for functions:

$sql = 'BEGIN :total := sum(:num1, :num2); END;';
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindParam(':total', $total);
$stmt->executeQuery([
    ':num1' => 6,
    ':num2' => 7
]);

$total will store the output

answered Sep 8, 2021 at 22:09

manix

14.7k11 gold badges74 silver badges112 bronze badges

Comments

lazar2038 · Accepted Answer · 2024-04-23 13:26:32Z

0

here is the working snippet. No need of $sql part, because we use the procedure, instead of using sql statements.

$result = null;
DB::connection('orange')->executeProcedure('OS_USR.OSOPR.p_get_multiplication',
[
    'res' => &$result,
    'a' => 2,
    'b' => 3,
]);
return $result;

Note that this snippet uses Oracle connection with OCI8 driver by yajra/laravel-oci8.

answered Apr 23, 2024 at 13:26

lazar2038

211 bronze badge

Collectives™ on Stack Overflow

How to call an Oracle stored procedure with output parameters using doctrine?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related