Sunday, March 25, 2018

Call Oracle PL/SQL returning CLOB from Laravel

By Hường Hana 10:00 PM , , Leave a Comment

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?

1 Answers

Answers 1

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

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment