Call Oracle PL/SQL returning CLOB from Laravel

Question

I am using the Laravel-OCI8 package to communicate with Oracle Database from Laravel. I am calling a function of oracle which returns the result as CLOB. Here is the Laravel Code:

$stmt = $this->pdo_obj->prepare("begin :result := test_pkg.get_data(:param1,:param2,:param3); end;");
$stmt->bindParam(':result', $result, \PDO::PARAM_STR);
$stmt->bindParam(':param1', $param1, \PDO::PARAM_STR);
$stmt->bindParam(':param2', $param2, \PDO::PARAM_STR);
$stmt->bindParam(':param3', $param3, \PDO::PARAM_STR);
$stmt->execute();
return response($result);

But I am getting an error : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I also tried following:

$response   = $this->oracle_obj->select("select test_pkg.get_data('$param1','$param2','$param3') as refc from dual");

But it's not possible to use the above calling in my case as there are DML operations inside my function. Is there any other method to call the function of oracle in Laravel using OCI8?

Does the Oracle function work in other contexts, e.g. from SQL*Plus or SQL Developer? Can you post the code from the function? My hunch is that it is unrelated the context you're using it in, and that the error is in fact in the function itself. — David Faber

Syed Ekram Uddin Syed Ekram Uddin · Accepted Answer · 2018-03-21T00:33:06

I think the problem is not cause for Laravel or related to use of any other method to call the function of oracle in Laravel. It's related to specify the correct explicit data type for the parameter while binding parameters using $stmt->bindParam method and setting the length of data type. According to official PHP documentation ..

To indicate that a parameter is an OUT parameter from a stored procedure, you must explicitly set the length.

Refer to the data_type and length specification from PDOStatement::bindParam PHP doc http://php.net/manual/en/pdostatement.bindparam.php

Just an example,

$sth->bindParam(':param1', $param1, PDO::PARAM_INT);
$sth->bindParam(':param2', $param2, PDO::PARAM_STR, 12);

Reference

Hope that helps

Call Oracle PL/SQL returning CLOB from Laravel

1 Answers