Jump to content

about ORA-06502 sql error


joymis
 Share

Recommended Posts

Hello,

I have some code like this

$sql = "BEGIN :ret := sifw.sf_todoList('38000', '', 'all'); END;";

$stid = oci_parse($conn, $sql);

oci_bind_by_name($stid, ':ret', $r);

oci_execute($stid);

I got a error : 「ORA-06502: PL/SQL: numeric or value error: character string buffer too small」

if I change oci_bind_by_name($stid, ':ret', $r) to oci_bind_by_name($stid, ':ret', $r, 200), it's work

but I don't want to set a current length, because I do not know OUT bind type and length.

how can I fixed this error?

Thanks

Link to comment
Share on other sites

because I do not know OUT bind type and length.

You need to figure that out, because you have to set a length:

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

Link to comment
Share on other sites

9 hours ago, justsomeguy said:

You need to figure that out, because you have to set a length:

but my OUT bind type maybe number or string or array, or have any function can get OUT bind length?

class DBObj{
  public function spExec($sql, $params){
    $stid = oci_parse($this->con, $sql);

    foreach ($params as $key => &$value) 
    {
        if($value == 'sp_return')
            // I'm not sure $len
            // could I use something function get OUT bind length?
            oci_bind_by_name($stid, $key, $r, $len);
        else
            oci_bind_by_name($stid, $key, $value);
    }

    oci_execute($stid, OCI_DEFAULT) or die($this->dbError($stid));

    return $r;
  }
}

$db = new DBObj();

// maybe this return number
$sql = "BEGIN :ret := aaa(:p1, :p2, :p3); END;";
$params = array(':ret' => 'sp_return', ':p1' => '1', ':p2' => '2', ':p3' => '3');
$result = $db->spExec($sql, $params);
echo $result;

// maybe this return string or array
$sql = "BEGIN :ret := bbb(); END;";
$params = array(':ret' => 'sp_return');
$result = $db->spExec($sql, $params);
echo $result;

 

Edited by joymis
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...