Jump to content

about ORA-06502 sql error


joymis

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

On 2017/6/10 at 0:39 AM, justsomeguy said:

If you don't know what the type will be, then use a length that will be long enough for anything.

ok, thank you for your help

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
×
×
  • Create New...