一个年代的悲剧 2015-09-13 13:46
浏览 2119

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\n");
    }

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

?>

可是在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脚本测试,就出错!
求高手指点!
__

  • 写回答

0条回答

    报告相同问题?

    悬赏问题

    • ¥30 这是哪个作者做的宝宝起名网站
    • ¥60 版本过低apk如何修改可以兼容新的安卓系统
    • ¥25 由IPR导致的DRIVER_POWER_STATE_FAILURE蓝屏
    • ¥50 有数据,怎么建立模型求影响全要素生产率的因素
    • ¥50 有数据,怎么用matlab求全要素生产率
    • ¥15 TI的insta-spin例程
    • ¥15 完成下列问题完成下列问题
    • ¥15 C#算法问题, 不知道怎么处理这个数据的转换
    • ¥15 YoloV5 第三方库的版本对照问题
    • ¥15 请完成下列相关问题!