Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06502: PL/SQL: numeric or value error: host bind array too small [message #291311] Thu, 03 January 2008 16:22 Go to next message
michrony
Messages: 2
Registered: January 2008
Junior Member
The following package works fine inside Oracle, calling demohash.test is OK. But when it is called from php, there is an error:
ORA-06502: PL/SQL: numeric or value error: host bind array too small

Actually, when the element of associative array Result is short enough, it works. But the whole point is passing long enogh strings!

Any idea what if anything can be done?

Thanks!


create or replace package demohash as
type biparam is table of varchar2(32000) index by binary_integer;
PROCEDURE doit(
frmCode in varchar2,
actCode in varchar2,
whatnam in biparam,
whatval in biparam,
res in out biparam
);
procedure test;
end demohash;
/
show error
---------------------------------------------------------------------------
create or replace package body demohash as
PROCEDURE doit(
frmCode in varchar2,
actCode in varchar2,
whatnam in biparam,
whatval in biparam,
res in out biparam
) is
begin
res(1) := '<result>
' ||frmCode || '
</result>';
res(2) := frmCode;
end doit;
---------------------------------------------------------------------------
procedure test is
whatnam biparam;
whatval biparam;
res biparam;
begin
whatnam(1) := 'A';
whatnam(2) := 'B';
whatval(1) := 1;
whatval(2) := 2;
doit('a', 'b',
whatnam,
whatval,
res
);

end test;
---------------------------------------------------------------------------
end demohash;
/
show error

...
$s = "begin demohash.doit(:frmCode, :actCode, :whatnam, :whatval, :res); end;";

$stmt = oci_parse($db, $s);

$rc['parse'] = $stmt!=false;

$req = array("request" => "how are you?");
$req = array("how are you?");

/*
$rc['count'] = count($req);
$rc['bind1'] = oci_bind_array_by_name($stmt, ":c1", $req,
64, -1, SQLT_CHR);
*/

$frmCode = "a";
$actCode = "b";
$whatnam = array("n1", "n2");
$whatval = array(1, 2);
$long = <<<END
1
2
3
END;
$req = array($long, $long);
$rc['frmCode'] = OCIBindByName($stmt, ":frmCode", $frmCode, 32);
$rc['actCode'] = OCIBindByName($stmt, ":actCode", $actCode, 32);
$rc['whatnam'] = oci_bind_array_by_name($stmt, ":whatnam", $whatnam, 64, -1, SQLT_CHR);
$rc['whatval'] = oci_bind_array_by_name($stmt, ":whatval", $whatval, 64, -1, SQLT_CHR);
$rc['res'] = oci_bind_array_by_name($stmt, ":res", $req, 64, -1, SQLT_CHR);

$rc['exec'] = oci_execute($stmt, OCI_DEFAULT);
$rc['errs'] = var_export(oci_error($stmt), true);

$res = var_export($rc, true) . var_export($req, true);
print $res;
...



Re: ORA-06502: PL/SQL: numeric or value error: host bind array too small [message #291315 is a reply to message #291311] Thu, 03 January 2008 16:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I do PERL a lot more than I do PHP.
In PERL an additional optional parameter needs to be supplied when a DB connection is opened so that "LONG" datatype can be supported.
Perhaps a similar "switch" is needed for PHP.
In any case this is NOT really an Oracle problem, per se.

You might get better & faster answer(s) on a PHP Forum.
Re: ORA-06502: PL/SQL: numeric or value error: host bind array too small [message #291385 is a reply to message #291311] Fri, 04 January 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel

[Updated on: Fri, 04 January 2008 01:18]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error: host bind array too small [message #291502 is a reply to message #291385] Fri, 04 January 2008 08:00 Go to previous message
michrony
Messages: 2
Registered: January 2008
Junior Member
[Make sure that lines of code do not exceed 80 characters when you format.]

Thanks a lot for valuable input. In fact, this is a good idea for source code formatting as well
Smile
Previous Topic: how to execute oracle procedure with OUT parameter
Next Topic: To create unique id for a group
Goto Forum:
  


Current Time: Tue Feb 11 11:09:22 CST 2025