ORA-06502: PL/SQL: numeric or value error: character string buffer too small - Executing using OCI interface

Question

I am completely flaberghasted and dont understand what i need to do to fix this error. I have a plsql procedure that accepts a varchar2 string and an OUT param which is a number.Can you pls help me as i am learning and new to plsql and php.

type of columns member_name is VARCHAR2(100) and member_id is NUMBER(20)

create or replace procedure GET_MEMBER_ID (V_MEMBER_NAME IN  VARCHAR2,V_MEMBER_ID OUT NUMBER ) AS 
BEGIN
SELECT member_id INTO V_MEMBER_ID
FROM mn_member WHERE member_name = V_MEMBER_NAME;
END;
/

i execute the above stored procedure from php as follows

   error_reporting(E_ALL);
   ini_set('display_errors', 1);
   $conn = oci_connect("$user","$password","$sid");

   $MEMBER_ID=0;
   $MEMBER_NAME='45390';
   echo gettype($MEMBER_NAME), "\n";
   echo gettype($MEMBER_ID), "\n";

   $sql_get_member_id = "BEGIN GET_MEMBER_ID(:MEMBER_NAME,:MEMBER_ID);END;";
   $stmt1 = oci_parse($conn,$sql_get_member_id);

   //  Bind the input parameter
   oci_bind_by_name($stmt1,':MEMBER_NAME',$MEMBER_NAME);
   oci_bind_by_name($stmt1,':MEMBER_ID',$MEMBER_ID);

   oci_execute($stmt1);
   echo "Member ID is ".$MEMBER_ID;
?>

This is the output i see in php

string integer Warning: oci_execute() [function.oci-execute]: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 in rtp2/test.php on line 26 Member ID is 0

Looks like your member_id in table is not number. Please post result of desc mn_member — Kacper
– Kacper, Commented Dec 8, 2016 at 18:21
yes you are right,member_id in table is NUMBER(20) and am wondering how do i specify that in the stored procedure as i am getting this error now when i specify V_MEMBER_ID OUT NUMBER(20) Error(1,74): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. — Sanjay Rao
– Sanjay Rao, Commented Dec 8, 2016 at 18:33
Specification is OK, you don't need to set up precision in return parameter just type is OK. I suspected it is varchar2 in table. What is type of member_name? — Kacper
– Kacper, Commented Dec 8, 2016 at 18:36

DCookie · Accepted Answer · 2016-12-08 20:20:39Z

1

I'm no PHP guy, but from the docs:

"You must specify maxlength when using an OUT bind so that PHP allocates enough memory to hold the returned value."

Maybe try something like:

   //  Bind the input parameter
   oci_bind_by_name($stmt1,':MEMBER_NAME',$MEMBER_NAME);
   oci_bind_by_name($stmt1,':MEMBER_ID',$MEMBER_ID,20,SQLT_INT);

Not sure about the SQLT_INT data type specification necessity.

answered Dec 8, 2016 at 20:20

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

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

1 Comment

Sanjay Rao Over a year ago

God Bless you !

Collectives™ on Stack Overflow

ORA-06502: PL/SQL: numeric or value error: character string buffer too small - Executing using OCI interface

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related