问题描述

PHP OCI执行oracle存储过程如何传递CLOB参数

_下面的代码有关于测试数据表test_clob,以及插入记录的存储过程test_p_saveclob 。代码如下：
#!/opt/php/bin/php -q
<?php
// 参考：http://www.golaravel.com/php/function.oci-new-descriptor.html
// 参考：http://php.net/manual/zh/function.oci-bind-by-name.php

/*
1)数据表 test_clob
SQL> desc test_clob
Name Null? Type

---

ID NUMBER
BIGSTR CLOB
SNAM VARCHAR2(20)

SQL>
2）存储过程
SQL> desc test_p_saveclob
PROCEDURE test_p_saveclob
Argument Name Type In/Out Default?

---

I_ID NUMBER(38) IN
I_BIGSTR CLOB IN
I_SNAM VARCHAR2 IN
O_COUNT NUMBER(38) OUT
O_RETURN NUMBER(38) OUT

SQL>

CREATE OR REPLACE PROCEDURE TEST_P_SAVECLOB
(
I_ID IN INT,
I_BIGSTR IN CLOB,
I_SNAM IN VARCHAR2,
O_COUNT OUT INT, --返回值导入的号码数量
O_RETURN OUT INT --返回值：0为导入成，-1为导入失败
)
AS
V_BIGSTR CLOB;
BEGIN
--INSERT INTO TEST_CLOB(ID,BIGSTR,SNAM) VALUES(I_ID ,I_BIGSTR ,I_SNAM);
--insert into TEST_CLOB(id,BIGSTR,SNAM) values(I_ID ,EMPTY_CLOB() ,I_SNAM) returning BIGSTR into I_BIGSTR;
INSERT INTO TEST_CLOB(ID,BIGSTR,SNAM) VALUES(I_ID ,EMPTY_CLOB() ,I_SNAM);
SELECT BIGSTR INTO V_BIGSTR FROM TEST_CLOB WHERE ID=I_ID;
DBMS_LOB.OPEN(V_BIGSTR,DBMS_LOB.LOB_READWRITE);
DBMS_LOB.WRITE(V_BIGSTR,LENGTH(I_BIGSTR),1,I_BIGSTR);
DBMS_LOB.CLOSE(V_BIGSTR);
O_COUNT := 1; --插入的个数
O_RETURN := 0; --插入成功
END;

3）pl/sql测试：
declare
O_COUNT int := 88;
O_RETURN int := -2;
begin
TEST_P_SAVECLOB(3,'11111111---','wjj',O_COUNT,O_RETURN);
commit;
end;

*/

//参数--------------
$I_ID = 5;
$I_BIGSTR = '---abc--';
$I_BIGSTR = str_repeat($I_BIGSTR,12);
$I_SNAM = 'wjj';
$O_COUNT = 88;
$O_RETURN = -2;

  $db_str = "172.29.3.191/mobile";
  $conn = oci_connect("帐号", "密码",$db_str);

   $sql_sp = "begin TEST_P_SAVECLOB(:I_ID ,:I_BIGSTR ,:I_SNAM ,:O_COUNT ,:O_RETURN); end;";
   $stmt = oci_parse($conn, $sql_sp);

   //输入参数
   oci_bind_by_name($stmt, ":I_ID", $I_ID ,32);
   oci_bind_by_name($stmt, ":I_SNAM", $I_SNAM ,64);
   //输出参数
   oci_bind_by_name($stmt, ":O_COUNT", $O_COUNT, 32);
   oci_bind_by_name($stmt, ":O_RETURN", $O_RETURN, 32);

//oci_execute($stmt, OCI_DEFAULT);

   //大数据CLOB： // Create a new lob descriptor object
   $textLob = oci_new_descriptor($conn, OCI_D_LOB);
   oci_bind_by_name($stmt, ":I_BIGSTR", $textLob, -1, OCI_B_CLOB);

   //$textLob->write($I_BIGSTR);
   $textLob->write($I_BIGSTR);
   oci_execute($stmt, OCI_DEFAULT);

/*

   // Execute the statement but do not commit
  oci_execute($stmt, OCI_DEFAULT);
   // Save the body of the blog entry to the CLOB
    if ( !$textLob->save($I_BIGSTR) ) {  //这里才是处理CLOB大数据的关键技术
        // Rollback the procedure
        oci_rollback($conn);
        die ("Error saving lob
");
    }

*/
// Everything OK so commit
oci_commit($conn);
echo $O_COUNT,"--",$O_RETURN,"
";

?>

可是在linux下面运行后，总是报错，如下：
vm-web1:/var/www/html/test # ./clob-proc2.php
PHP Warning: OCI-Lob::write(): OCI_INVALID_HANDLE in /var/www/html/test/clob-proc2.php on line 85
?
Warning: OCI-Lob::write(): OCI_INVALID_HANDLE in /var/www/html/test/clob-proc2.php on line 85
PHP Warning: oci_execute(): ORA-22275: invalid LOB locator specified
ORA-06512: at "YLCQ.TEST_P_SAVECLOB", line 17
ORA-06512: at line 1 in /var/www/html/test/clob-proc2.php on line 86

Warning: oci_execute(): ORA-22275: invalid LOB locator specified
ORA-06512: at "YLCQ.TEST_P_SAVECLOB", line 17
ORA-06512: at line 1 in /var/www/html/test/clob-proc2.php on line 86
88---2
vm-web1:/var/www/html/test #
问题：在sqlplus中用匿名过程调用存储过程test_p_saveclob，一切都正常，数据表中可以新增一条记录。可是用上面的php脚本测试，就出错！
求高手指点！
__

解决方案

Oracle执行参数化SQL语句和存储过程