PDO takes Oracle lob large field when the data volume is too large to be taken out of the problem solution

  • 2020-06-03 08:40:10
  • OfStack

First, create a stored procedure get_clob:
t_name: The table name to query; f_name: The field name to query; u_id: Primary key of table, query condition; l_pos: Start of interception; l_amount: Intercept length;
CREATE OR REPLACE PROCEDURE get_clob(t_name in varchar2, f_name in varchar, u_id in integer, l_pos in integer, l_amount in BINARY_INTEGER, ReturnValue out varchar2) is
rule_xml clob;
l_buffer varchar2(3999);
execute immediate 'select ' ||f_name|| ' from ' ||t_name|| ' where id=:1' into rule_xml using u_id;
DBMS_LOB.read(rule_xml, l_amount_, l_pos, l_buffer);
ReturnValue := l_buffer;
end get_clob;
Then there is the handler for php:
$content = "";
$num = 0; //clob field length
$stmt = $oracle- > prepare("select length(content) as num from test where id = $id");
if ($stmt- > execute()) {//zjh is the condition of the query
$row = $stmt- > fetch();
$num = $row['NUM'];
$start = 1; // Initialize the starting position
$len = 2500; // Intercept length
$t_name = 'test'; // Operation table name
$f_name = 'content'; // Name of clob field to be queried
while ($start < = $num){
$sql = "begin get_clob(?,?,?,?,?,?); end;";
$stmt = $oracle- > prepare($sql);
$stmt- > bindParam(1, $t_name, PDO::PARAM_STR, 100);
$stmt- > bindParam(2, $f_name, PDO::PARAM_STR, 100);
$stmt- > bindParam(3, $id, PDO::PARAM_STR, 100);
$stmt- > bindParam(4, $start, PDO::PARAM_STR, 100);
$stmt- > bindParam(5, $len, PDO::PARAM_STR, 100);
$stmt- > bindParam(6, $ret, PDO::PARAM_STR, 5000);
$stmt- > execute();
$content .= $ret;
$oracle = null;
This is the complete solution. This method is not my first, and most people who have worked on pb are familiar with it.
php operation Oracle data network is relatively few, can solve the problem is not much, sent to share with you 1, there must be other better solutions, welcome to discuss with you 1.

Related articles: