php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #48328 While using oci_execute on a stored proc and sequence goes above 999
Submitted: 2009-05-19 10:09 UTC Modified: 2009-05-19 16:53 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:0 of 1 (0.0%)
From: surajsrinivasan at hsbc dot co dot in Assigned:
Status: Not a bug Package: OCI8 related
PHP Version: 5.2.9 OS: Windows
Private report: No CVE-ID: None
 [2009-05-19 10:09 UTC] 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

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-05-19 16:39 UTC] 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 16:53 UTC] 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);
 [2014-02-05 10:15 UTC] nm dot nowytestowyuzytkownik at gmail dot com
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
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu May 02 08:01:32 2024 UTC