Output parameter - how to bind in oci8

[pszemo]

Summary of problem or feature request

Would like to use Oracle Stored Procedure with output parameter SP: DECLARE out_var NUMBER; BEGIN test_params (1,2,out_var); DBMS_OUTPUT.PUT_LINE('Result:'||out_var); END

call from Laravel:

$procedureName = 'test_params';

    $bindings = [
        'p_value1' => 1,
        'p_value2' => 2,
        'po_result' => 0
    ];

    $result = DB::executeProcedure($procedureName, $bindings);
    print_r($result);

System details

CentOS Apache/2.4.6 PHP/7.1.27 Laravel 5.8.35 OCI8 5.8.2

[Jon4t4n]

Hey

I have never used the DB::executeProcedure helper method. But I did some testing and the example below should work.

You need to define a variable and pass it by reference. Also when defining an out parameter you also need to define the length.

$result = null;

$bindings = [
    'p_value1' => 1,
    'p_value2' => 2,
    'po_result' => [
        'value' => &$result,
        'length' => 1000,
    ],
];

DB::executeProcedure($procedureName, $bindings);

[pszemo]

Thanks, but doesn't work correctly. In your example returns true. Result of prcedure is sum of $val1 and $val2 When used PDO like below: (int) $val1 = 20; (int) $val2 = 20; (int) $val3 = 20;

$pdo = DB::getPdo();
$result = null;
$stmt = $pdo->prepare("begin test_params(:val1,:val2,:val3); end;");
$stmt->bindParam(':val3', $val3, \PDO::PARAM_INT|\PDO::PARAM_INPUT_OUTPUT);
$stmt->bindParam(':val1', $val1, \PDO::PARAM_INT);
$stmt->bindParam(':val2', $val2, \PDO::PARAM_INT);
$stmt->execute();

dd($val3);

returns 40

[Jon4t4n]

The DB:executeProcedure method returns a boolean. True on success, otherwise false. The result returned by the procedure is stored in the $result variable.

Like this

$result = null;

$bindings = [
    'p_value1' => 1,
    'p_value2' => 2,
    'po_result' => [
        'value' => &$result,
        'length' => 1000,
    ],
];

$succeeded = DB::executeProcedure($procedureName, $bindings);

if($succeeded) {
    dd($result);
}
else {
    dd("Failed");
}

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.