ORA-6502，来自WEB

Question

我有一个Oracle DB的问题。

 <?php
    require_once 'includes/conn.php';
    function connect_db()
    {
        if ($c=oci_pconnect(uname,pwd, host,'AL32UTF8'))
            return $c;
        else
            die( "ERROR");
    }
    $conn=connect_db();

    $query = "BEGIN :ds_id :=DS.REG_DS1(:F_NAME);END;";
    $stmt=oci_parse($conn,$query);
    $f_name='John Doe';
    $ds_id=-1;

    oci_bind_by_name($stmt, ":ds_id", $ds_id);
    oci_bind_by_name($stmt, ":F_NAME", $f_name);
    if(oci_execute($stmt))
    {
        echo 'good';
    }
    else
        print_r(oci_error($stmt));
?>

下面是函数REG_DS1

FUNCTION REG_DS1(F_NAME IN VARCHAR) RETURN NUMBER AS
  DS_ID NUMBER(8,0):=9988;
BEGIN
--    INSERT INTO TEST VALUES(F_NAME,SYSDATE);
    RETURN DS_ID;
END REG_DS1;

当我尝试从Sql Developer执行此函数时，它运行时没有任何问题。但是如果我从上面的PHP脚本执行，它会给我错误：

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 [offset] => 0 [sqltext] => BEGIN :ds_id :=DS.REG_DS1(:F_NAME);END; )

如果我将DS_ID变量更改为另一个小于100的数字，它在这两个变量中都能很好地工作。但是如果我把它设置成一个大于99的数字，它就会从php脚本中得到错误。

会有什么问题呢？

Answer 1

--jst idea，这不是solutioN

变量解密或某些定义问题

oci_connect($ODBuser,$ODBpass,$ODBhost,$db_charset);

应该尝试这样的，而不是

oci_pconnect(uname,pwd, host,'AL32UTF8')

示例代码：

-- just idea 
<?php

$file = "../script/param.CML";
$encryptObj = new cast128;
$encryptObj->setkey("SABBIllustrateKey");

$fp=fopen($file,'r');
$strContent = fread($fp, filesize($file));
fclose($fp);

$strContent=$encryptObj->decrypt($strContent);

$ArConnect=split("   ",$strContent);
$OraService=$ArConnect[8];
$OraUser=$ArConnect[9];
$OraDBpass=$ArConnect[10];
$db_charset = 'AL32UTF8';


$cursor=oci_connect($OraUser,$OraDBpass,$OraService,$db_charset);

if(!$cursor)
{
 echo "<br>Error In connection:- reason<BR>";
 $e = oci_error();  // For oci_connect errors pass no handle
}

?>

<?php

$file = "../script/param.CML";
$encryptObj = new cast128;
$encryptObj->setkey("SABBIllustrateKey");

$fp=fopen($file,'r');
$strContent = fread($fp, filesize($file));
fclose($fp);

$strContent=$encryptObj->decrypt($strContent);

$con=split("   ",$strContent);
$OraService=$con[8];
$OraUser=$con[9];
$OraDBpass=$con[10];
$db_charset = 'AL32UTF8';


$cursor=oci_connect($OraUser,$OraDBpass,$OraService,$db_charset);

if(!$cursor)
{
 echo "<br>Error In connection:- reason<BR>";
 $e = oci_error();  // For oci_connect errors pass no handle
}

?>

Answer 2

伙计们，我终于自己解决问题了。问题在于返回值的大小可变

通过更改

oci_bind_by_name($stmt, ":ds_id", $ds_id);

至

oci_bind_by_name($stmt, ":ds_id", $ds_id,10,SQLT_INT );

问题已经解决了。