I have an application developed with the Laravel php framework. And I use the following package to work with my oracle database. https://github.com/yajra/laravel-oci8
I have the following function in oracle:
function my_func(uid in number, json in out nocopy clob) return number is
And in php I call it that
$result = '';
$pdo = DB::connection('oracle')->getPdo();
$stmt = $pdo->prepare("begin :result := my_func(:uid, :json); end;");
$stmt->bindParam(':result', $result, \PDO::PARAM_INT);
$stmt->bindParam(':uid', $uid, \PDO::PARAM_INT);
$stmt->bindParam(':json', $json, \PDO::PARAM_STR, 1000000);
$stmt->execute();
return response()->json(json_decode($json), ($result === 1) ? 200 :
400);
When the output of 'json' is smaller than 32767 characters, then the code works. But when it is greater the following exception occurs:
Oci8Exception {#615
#message: """
Error Code : 6502
Error Message : ORA-06502: PL/SQL: erro numérico ou de valor
System details Oracle 11g
Ubuntu 18.04
PHP 7.2
Laravel 5.5
Laravel-OCI8 5.5
I was able to develop a solution. Not sure if it is the best, but it worked.
$result = $lob = '';
$pdo = DB::connection('oracle')->getPdo();
$stmt = $pdo->prepare("declare v_uid number(10) := :uid; v_data clob := :data; begin :result := my_func(v_uid, v_data); :lob := v_data; end;");
$stmt->bindParam(':result', $result, \PDO::PARAM_INT);
$stmt->bindParam(':uid', $uid, \PDO::PARAM_INT);
$stmt->bindParam(':data', $data, \PDO::PARAM_STR);
$stmt->bindParam(':lob', $lob, SQLT_CLOB);
$stmt->execute();
$json = $lob->read($lob->size());
return response()->json(json_decode($json), ($result === 1) ? 200 : 400);