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
– Barbaros Özhan, Commented Dec 21, 2017 at 8:13
Possible duplicate of ORA-06502: PL/SQL: numeric or value error: character string buffer too small — Cleptus
– Cleptus, Commented Dec 21, 2017 at 8: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
– Daniel Hilgarth, Commented Dec 21, 2017 at 8:23

Wernfried Domscheit · Accepted Answer · 2017-12-21 09:31:26Z

3

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.

answered Dec 21, 2017 at 9:31

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

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

Comments

jtate · Accepted Answer · 2019-12-01 06:44:27Z

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 1, 2019 at 6:44

jtate

2,7067 gold badges28 silver badges37 bronze badges

answered Dec 1, 2019 at 2:25

Saurabh Jain

112 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related