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 test@tesst.com';
END;

What is the purpose of specifying a size of 9999999999999999999? — OldProgrammer, Aug 21 '14 at 00:36

score 5 · Accepted Answer · answered Aug 21 '14 at 13:55

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 '14 at 13:55

Patrick Hofman

153,850
22
249
325

Yes, it does. I even tried the new Oracle.ManagedDataAccess, but it fails too. – Patrick Hofman Aug 21 '14 at 14:05
This didn't work for me but I'm not going to downvote as it's clear that it works for some folks. – Dan Atkinson Sep 12 '16 at 15:18
@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. – Patrick Hofman Sep 12 '16 at 16:26
@PatrickHofman When trying both solutions, I still get `ORA-06502: PL/SQL: numeric or value error: NULL index table key value`. – Dan Atkinson Sep 12 '16 at 17:01
@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](http://www.orafaq.com/forum/t/14998/). – Patrick Hofman Sep 12 '16 at 17:11
@PatrickHofman I asked our DBA about it and he assures me that the code is okay. I know SQL but don't know the finer points of PLSQL so I have to take him at his word. Also, if I execute the same call via SQLPlus or Toad for example, it *does* work, so it does suggest that something isn't correct on the ODP side. All I'm doing is passing in a string so nothing fancy in the request. – Dan Atkinson Sep 13 '16 at 09:22

score 0 · Answer 2 · answered Jul 11 '23 at 10:14

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 '23 at 10:14

Rimi

71
2

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

2 Answers2