Oracle Data Access ORA-06512: character string buffer too small

Question

When I call GetTest I get this error:

string buffer too small ORA-06512

This my c# method:

public string GetTEST()
{
    using (var conn = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Dbconnection"].ConnectionString))
    {
        OracleCommand cmd = new OracleCommand("Package.GetTEST");
        cmd.BindByName = true;
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("P_OUT_MESSAGE", OracleDbType.Varchar2,1000,ParameterDirection.Output);

        cmd.ExecuteNonQuery();

        var t = cmd.Parameters["P_OUT_MESSAGE"].Value;
    }
}

Oracle Procedure:

PROCEDURE GetTEST
(
  P_OUT_MESSAGE    OUT VARCHAR2 
)
IS
BEGIN
  p_out_message := 'Un problème a été signalé pour votre propriété. Veuillez communiquer avec le Service de l''évaluation au 418 111-7878 ou à l''adresse [email protected]';
END;

What is the purpose of specifying a size of 9999999999999999999? — OldProgrammer
– OldProgrammer, Commented Aug 21, 2014 at 0:36

Patrick Hofman · Accepted Answer · 2014-08-21 13:55:43Z

5

This is obviously not what you want, but it seems that ODP.NET uses the length of the parameter at the .NET side as the length of the out parameter...

This will fix your issue:

cmd.Parameters.Add("P_OUT_MESSAGE", OracleDbType.Varchar2, 32767, "x".PadRight(500, 'x'), ParameterDirection.Output);

But this is nicer, and although not entirely correct, it works:

cmd.Parameters.Add("P_OUT_MESSAGE", OracleDbType.Clob, ParameterDirection.Output);

Or, even better, if possible, avoid the use of out parameters and use scalar return values or table functions.

answered Aug 21, 2014 at 13:55

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

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

6 Comments

Patrick Hofman Over a year ago

Yes, it does. I even tried the new Oracle.ManagedDataAccess, but it fails too.

Dan Atkinson Over a year ago

This didn't work for me but I'm not going to downvote as it's clear that it works for some folks.

Patrick Hofman Over a year ago

@dan: thanks for commenting. Can you elaborate on the 'not working' part? Maybe you can ask a new question, refer to this one and maybe we can find something that does work for you.

Dan Atkinson Over a year ago

@PatrickHofman When trying both solutions, I still get ORA-06502: PL/SQL: numeric or value error: NULL index table key value.

Patrick Hofman Over a year ago

@dan can it be related to another problem in your code. It seems that it is related to the function's internal code. See here.

|

Rimi · Accepted Answer · 2023-07-11 10:14:10Z

0

You must use 5 parameters for the OUT parameter of procedure. So I added null here:

cmd.Parameters.Add("P_OUT_MESSAGE", OracleDbType.Varchar2, 1000, null, ParameterDirection.Output)

with 4 it treats 1000 as object value and not as size.

answered Jul 11, 2023 at 10:14

Rimi

811 silver badge2 bronze badges

Collectives™ on Stack Overflow

Oracle Data Access ORA-06512: character string buffer too small

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related