ID: 48328 User updated by: surajsrinivasan at hsbc dot co dot in Reported By: surajsrinivasan at hsbc dot co dot in Status: Bogus Bug Type: OCI8 related Operating System: Windows PHP Version: 5.2.9 New Comment:
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); Previous Comments: ------------------------------------------------------------------------ [2009-05-19 16:39:40] [email protected] ------------------- 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 http://bugs.php.net/?id=48328&edit=1
