Character string buffer too small error while calling Oracle function using C#

Question

plsql numeric value error while calling the oracle function from C# - please tell me how to remove that error.

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

Code:

create or replace function testdotnet(h1 varchar2)
 return varchar2   
 as
x varchar2(250);
begin
      select 'hello' into x from dual;
      return x;
end;

C# code:

string CommandStr = "APPS.testdotnet";

using (OracleConnection conn = new OracleConnection("User Id=apps;Password=***;Data Source=***"))
using (OracleCommand cmd = new OracleCommand(CommandStr, conn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("h1", OracleDbType.Varchar2).Value = "aaa";
    cmd.Parameters.Add("x", OracleDbType.Varchar2, 40000).Direction = ParameterDirection.ReturnValue;
    cmd.Parameters["x"].Size = 255;
    cmd.Parameters["h1"].Size = 255;

    conn.Open();
    cmd.ExecuteNonQuery();

    Response.Write( cmd.Parameters["x"].Value.ToString());
}

please try to you change `250` to `255` for `x varchar2(250);`, and look whether the error persists ... — Barbaros Özhan, Dec 21 '17 at 08:13
Possible duplicate of [ORA-06502: PL/SQL: numeric or value error: character string buffer too small](https://stackoverflow.com/questions/18731560/ora-06502-pl-sql-numeric-or-value-error-character-string-buffer-too-small) — Cleptus, Dec 21 '17 at 08:14
You are setting the size to 40000 when you add x as a parameter and afterwards you reset that size to 255. Why? — Daniel Hilgarth, Dec 21 '17 at 08:23
@flexbuzzhour if you have edited the code in relation to the comments here, please edit the question so it reflects the current "not working" state of your code, not the code as it was before suggestions were made — Caius Jard, Dec 21 '17 at 09:18
Take a look at https://stackoverflow.com/questions/23199713/c-sharp-call-oracle-stored-function , modify your function to return a number and modify your code using that accepted answer as a model. If it works, then we know the problem is with the return varchar. If not, the problem is the input varchar (I suspect the return varchar also) — Caius Jard, Dec 21 '17 at 09:22
Why do you make `select ... into from dual;`? Simply write `return 'hello';` or `x := 'hello'; return x;` — Wernfried Domscheit, Dec 21 '17 at 09:27

score 2 · Answer 1 · answered Dec 21 '17 at 09:31

Maximum size of VARCHAR2 is 32,767 bytes, 40,000 is not possible.

Change

cmd.Parameters.Add("x", OracleDbType.Varchar2, 40000).Direction = ParameterDirection.ReturnValue;

to

cmd.Parameters.Add("x", OracleDbType.Varchar2, 32767).Direction = ParameterDirection.ReturnValue;

or use CLOB if you need bigger data.

score 0 · Answer 2 · edited Dec 01 '19 at 06:44

0

My stored procedure had "keys" as an output parameter in definition.

command.Parameters.Add("keys", OracleDbType.Varchar2, 32767).Direction = ParameterDirection.Output;

That fixed it for me, passing the size of output varchar2 variable along with the type.

I hope this helps someone looking for the solution of this error.

edited Dec 01 '19 at 06:44

jtate

2,612
7
25
35

answered Dec 01 '19 at 02:25

Saurabh Jain

11
2

Character string buffer too small error while calling Oracle function using C#

2 Answers2