Oracle.Dataaccess error ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Question

I am invoking a stored proc from .NET app. The proc returns an out parameter of type Varchar2. To fet ch the out parameter I am passing the parameter to the command as OracleParameter:

parm12 = new OracleParameter("testkey"
                              , OracleDbType.Varchar2
                              , out2
                              , ParameterDirection.Output);

When I execute the proc I am receiving an error

PL/SQL: numeric or value error: character string buffer too small.

Any help highly appreciated!!!

Answer 1

Just Google IT :-)

http://www.google.com/search?q=character+string+buffer+too+small&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:ru:official&client=firefox

Answer 2

In your code out2 is the argument which specifies the length of the parameter. So check the value in that variable, because apparently it is not long enough for procedure's output.

Answer 3

Found the answer.

For the OUT parameter i declared the size to max of varchar - 32767 and it started to work.

To simplify, the stored proc returns a parameter OUT of type VARCHAR2. But to consume that output from .NET i was passing VARCHAR2 without any size. So the buffer space allocated to recieve the reurn value was 0 bytes. When the proc returns the value more than allocated buffer which is 0 bytes it errors out.

So i specified the max of VARCHAR2-32767 in the C# code and it started to work :).