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

Question

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

You need to change the procedure.... you're assigning one of your parameters to a local variable in that procedure (or in something that procedure calls etc). The local variable does not have the correct datatype to accept what you are passing... you're going to have to change it. — Ben
– Ben, Commented Jan 29, 2014 at 12:45
How should I change the procedure? am a php developer so I dont really know.. — vamsi
– vamsi, Commented Jan 29, 2014 at 13:00
If you post the procedure then someone will be able to help... changing it might have knock on effects though so it's best to ask the person responsible for it. — Ben
– Ben, Commented Jan 29, 2014 at 13:22

Code Lღver · Accepted Answer · 2014-04-14 12:01:10Z

7

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

edited Apr 14, 2014 at 12:01

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

answered Apr 14, 2014 at 11:44

Nardus Grobler

711 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vincenzo Maggio · Accepted Answer · 2014-01-29 13:39:55Z

0

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.

answered Jan 29, 2014 at 13:39

Vincenzo Maggio

3,86928 silver badges42 bronze badges

Comments

Franc Drobnič · Accepted Answer · 2016-06-07 13:12:13Z

0

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.

answered Jun 7, 2016 at 13:12

Franc Drobnič

1,10512 silver badges15 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related