CODEWITHSUNDEEP
phporacle-databasestored-procedures
vamsi
vamsi

Reputation: 1586

numeric or value error: character string buffer too small - PHP + Oracle

Below is my procedure

NOBL_TEC_TEST_PROC(v1 number,v2 out number)

my model function

public function testProc()
{
    $result = '';
    $db = "orcl";
    $conn = oci_connect("***", "****", $db);
    $sql = 'begin NOBL_TEC_TEST_PROC(222, :seqno); end;';
    $stmt = oci_parse($conn,$sql);
    oci_bind_by_name($stmt, ':seqno', $result);
    oci_execute($stmt);

    return $result;

}

if I give the IN value <10 it is working fine, but when I try to give IN value >10 it is giving following error. 

<b>Warning</b>:  oci_execute(): ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 in....

How can I fix this? Any help? Thanks

Upvotes: 1

Views: 3496

Answers (3)

Franc Drobnič
Franc Drobnič

Reputation: 1085

Just for reference: this error was raised also when a parameter was declared as "IN OUT". Since we use refcursor to retrieve the result such declaration was in fact not needed and was changed to "IN" only. This resolved our issue.

Upvotes: 0

Nardus Grobler
Nardus Grobler

Reputation: 71

I Know its a little late, but you need to add your buffer size to your oci_bind_by_name

oci_bind_by_name($stmt, ':seqno', $result,2000);

This should solve your issue

Upvotes: 7

Vincenzo Maggio
Vincenzo Maggio

Reputation: 3869

possibly in the db the procedure parameter IN has parameter VARCHAR2(1) or something like that.

A number above 10 is two characters so an error is raised.

You should simply access the source of the Oracle Procedure, change the type of the parameter in the header and body of the procedure and recompile it.

Upvotes: 0

Related Questions