Bug #48328 [Com]: While using oci_execute on a stored proc and sequence goes above 999

Edit report at https://bugs.php.net/bug.php?id=48328&edit=1 ID: 48328 Comment by: nm dot nowytestowyuzytkownik at gmail dot com Reported by: surajsrinivasan at hsbc dot co dot in Summary: While using oci_execute on a stored proc and sequence goes above 999 Status: Not a bug Type: Bug Package: OCI8 related Operating System: Windows PHP Version: 5.2.9 Block user comment: N Private report: N New Comment: i've had this problem, and solution is quite simple when you declare NUMBER value, or any other you NEED TO ALWAYS ADD SIZE OF VAR (LENGHT) $stid = OCIParse($conn, $sql); oci_bind_by_name($stid, ':p1', $p_id, 20); // for example 20 when its number when you forgot add "20" and result is below 1000 then it should be fine, but when your result number is larger then 1000 it will stop working probably because default lenght is 3 Previous Comments: ------------------------------------------------------------------------ [2009-05-19 16:53:40] surajsrinivasan at hsbc dot co dot in Thanks sixd. I just went through your comment at http://www.php.net/manual/en/function.oci-bind-by-name.php#83102 and that makes sense for this issue. Anyway, here is the bind call: $seqid = "2000"; $sSQL = "BEGIN sp_name(:seqid); END;"; $stmt = oci_parse($conn , $sSQL); oci_bind_by_name($stmt, ":outid" , $seqid ); oci_execute($stmt, OCI_DEFAULT); ------------------------------------------------------------------------ [2009-05-19 16:39:40] sixd@php.net ------------------- Not enough information was given to accurately diagnose the issue. In particular, your bind call wasn't shown. For "out" binds, where data is returned out of PL/SQL or SQL, always specify a bind length. See my user comment in http://www.php.net/manual/en/function.oci-bind-by-name.php#83102 ------------------------------------------------------------------------ [2009-05-19 10:09:27] surajsrinivasan at hsbc dot co dot in Description: ------------ I execute a stored procedure (code listed below). The stored proc does several things, one of which is to fetch the next value in a sequence. It works fine except when the sequence value crossess 999. I tested for all cases by resetting the sequence initial value to 1, 2000, 50000. The stored procedure works perfectly. No issues. The PHP works fine only when the output paremeter (which is the sequence value) is initialised to a value > 1000 Has anyone come across this before? The sequence has no issues as it has a max limit > 99999999999. I tried to return the sequence value as both number and varchar2, but both have the same issue. Reproduce code: --------------- This works for sequence value <1000 but not >= 1000: $seqid = ""; $sSQL = "BEGIN sp_name(:seqid); END;"; $stmt = oci_parse($conn , $sSQL); ... oci_execute($stmt, OCI_DEFAULT); -> If ret var is > 999, gives "<b>Warning</b>: oci_execute() [<a href='function.oci-execute'>function.oci-execute</a>]: ORA-06502: PL/SQL: numeric or value error: character string buffer too small" This works for all cases: $seqid = "2000"; $sSQL = "BEGIN sp_name(:seqid); END;"; $stmt = oci_parse($conn , $sSQL); ... oci_execute($stmt, OCI_DEFAULT); Expected result: ---------------- Should work fine without needing to initialise $seqid Actual result: -------------- Have to initialise $seqid to a value > 1000 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=48328&edit=1