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

With the annoouncement from Microsoft on deprecating System.Data.OracleClient, i started to use Oracle client, ORACLE.DATAAccess and it has many uncovered challenges. i am posting one of the challange i faced in utilizing ORACLE.DATAAccess and the resolution.

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

Finally got it resolved. The problem is the proc returns two parms as OUT - RAW and VARCHAR2.

From C#, we can pass input with VARCHAR2 and the size of VARCHAR2 is adjusted based on the input string length. But for the Output, C# allocates 0 bytes - so the step in the PROC that assigns value to the output parm is throwing this error.

To Fix this error i assigned the MAX length of VARCHAR2 for the OUT parm - eventhough i dont like it :) and it started to work.

So if you know the size of the output parameter, you can pass the size appropriately. but for arbitraty length output we may have to pass the max of datatypes's length.