ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小-使用OCI接口执行 [英] ORA-06502: PL/SQL: numeric or value error: character string buffer too small - Executing using OCI interface

问题描述

我完全感到困惑，不明白我需要做些什么来解决此错误.我有一个plsql程序，该程序接受varchar2字符串和一个OUT参数，该参数是一个数字.当我正在学习并且是plsql和php的新手时，能否请我帮忙.

I am completely flaberghasted and dont understand what i need to do to fix this error. I have a plsql procedure that accepts a varchar2 string and an OUT param which is a number.Can you pls help me as i am learning and new to plsql and php.

member_name列的类型为VARCHAR2(100)，member_id为NUMBER(20)

type of columns member_name is VARCHAR2(100) and member_id is NUMBER(20)

create or replace procedure GET_MEMBER_ID (V_MEMBER_NAME IN  VARCHAR2,V_MEMBER_ID OUT NUMBER ) AS 
BEGIN
SELECT member_id INTO V_MEMBER_ID
FROM mn_member WHERE member_name = V_MEMBER_NAME;
END;
/

我从php执行上述存储过程，如下所示

i execute the above stored procedure from php as follows

   error_reporting(E_ALL);
   ini_set('display_errors', 1);
   $conn = oci_connect("$user","$password","$sid");

   $MEMBER_ID=0;
   $MEMBER_NAME='45390';
   echo gettype($MEMBER_NAME), "\n";
   echo gettype($MEMBER_ID), "\n";

   $sql_get_member_id = "BEGIN GET_MEMBER_ID(:MEMBER_NAME,:MEMBER_ID);END;";
   $stmt1 = oci_parse($conn,$sql_get_member_id);

   //  Bind the input parameter
   oci_bind_by_name($stmt1,':MEMBER_NAME',$MEMBER_NAME);
   oci_bind_by_name($stmt1,':MEMBER_ID',$MEMBER_ID);

   oci_execute($stmt1);
   echo "Member ID is ".$MEMBER_ID;
?>

这是我在php中看到的输出

This is the output i see in php

字符串整数 警告:oci_execute()[function.oci-execute]:ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小ORA-06512:在第26行的rtp2/test.php中的第1行 会员编号为0

string integer Warning: oci_execute() [function.oci-execute]: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 in rtp2/test.php on line 26 Member ID is 0

推荐答案

我不是PHP专家，但来自

I'm no PHP guy, but from the docs:

使用OUT绑定时必须指定maxlength，以便PHP分配足够的内存来保存返回的值."

"You must specify maxlength when using an OUT bind so that PHP allocates enough memory to hold the returned value."

也许尝试类似的事情:

   //  Bind the input parameter
   oci_bind_by_name($stmt1,':MEMBER_NAME',$MEMBER_NAME);
   oci_bind_by_name($stmt1,':MEMBER_ID',$MEMBER_ID,20,SQLT_INT);

不确定SQLT_INT数据类型规范的必要性.

Not sure about the SQLT_INT data type specification necessity.

这篇关于ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小-使用OCI接口执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！