ora numeric or value error: character string buffer too small RSS

• • Reply
  

  pasluc7469

  0 Points

  12 Posts

  ora numeric or value error: character string buffer too small

  Feb 25, 2013 09:16 AM|LINK

  Hi experts, I've a problem with calling a function by odp.net, and I can't solve it!!! I searched in the forum, but nothing!!!

  the error is the following

  {"ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 1"}

  I tryed to compile the project in x86, in "any cpu", but it's the same!!

  this is the code:

  protected void costumer_SelectedIndexChanged(object sender, EventArgs e)
          {
              Oracle.DataAccess.Client.OracleConnection oraconn = new Oracle.DataAccess.Client.OracleConnection("DATA SOURCE=localhost;PERSIST SECURITY INFO=True;USER ID=PROVA; PASSWORD=xxxxxxxxxxx");
              Oracle.DataAccess.Client.OracleCommand oraComm = new Oracle.DataAccess.Client.OracleCommand("GET_COSTUMER_ADDRESS", oraconn);
              oraComm.Parameters.Add("costumer_address", Oracle.DataAccess.Client.OracleDbType.Varchar2, ParameterDirection.ReturnValue);
       
              oraComm.CommandType = CommandType.StoredProcedure;
              
             
                  oraconn.Open();
                 oraComm.ExecuteNonQuery();
                 var a = 0;
                 string costAddress = oraComm.Parameters["costumer_address"].Value.ToString();
              
              
              oraconn.Dispose();
          }

  .. and the function...

  create or replace 
  function GET_COSTUMER_ADDRESS
  return varchar2 is
  
  costumerId number;
  costumer_address varchar2(50);
  
  BEGIN
  
  
  --costumerId := cost_id;
  costumer_address := 'my_address';
  return costumer_address;
  
  end GET_COSTUMER_ADDRESS;

  
  
  
  Hope someone can help me!!!

  thanks in advance.

• • Reply
  

  smirnov

  All-Star

  23670 Points

  4051 Posts

  Re: ora numeric or value error: character string buffer too small

  Feb 25, 2013 09:27 AM|LINK

  It sounds like you are missing quotations in strings or supplying a string value which is not enclosed with balance quotes or the value is too large (>50). 

  http://www.orafaq.com/forum/t/10140/2/

  • Edited by smirnov on Feb 25, 2013 09:29 AM
• • Reply
  

  pasluc7469

  0 Points

  12 Posts

  Re: ora numeric or value error: character string buffer too small

  Feb 25, 2013 09:51 AM|LINK

  Thank you smirnov, I tried ti change the value, but nothing changed, How can i fix the issue?
• • Reply
  

  smirnov

  All-Star

  23670 Points

  4051 Posts

  Re: ora numeric or value error: character string buffer too small

  Feb 25, 2013 10:25 AM|LINK

  Did you try to execute that function directly from Oracle?

• • Reply
  

  pasluc7469

  0 Points

  12 Posts

  Re: ora numeric or value error: character string buffer too small

  Feb 25, 2013 02:04 PM|LINK

  smirnov

  Did you try to execute that function directly from Oracle?

  yes, executing that function from Oracle, it works fine...

• • Reply
  

  pasluc7469

  0 Points

  12 Posts

  Re: ora numeric or value error: character string buffer too small

  Feb 26, 2013 12:48 PM|LINK

  if i change the returned value (in the function) from varchar2 in number, it works fine with this code...

  protected void costumer_SelectedIndexChanged(object sender, EventArgs e)
          {
              Oracle.DataAccess.Client.OracleConnection oraconn = new Oracle.DataAccess.Client.OracleConnection("DATA SOURCE=localhost;PERSIST SECURITY INFO=True;USER ID=PROVA; PASSWORD=p070774_ninO");
              Oracle.DataAccess.Client.OracleCommand oraComm = new Oracle.DataAccess.Client.OracleCommand("GET_COSTUMER_ADDRESS", oraconn);
              oraComm.Parameters.Add("costumer_address", Oracle.DataAccess.Client.OracleDbType.Int32, ParameterDirection.ReturnValue);
       
              oraComm.CommandType = CommandType.StoredProcedure;
              
             
                  oraconn.Open();
                 oraComm.ExecuteNonQuery();
                 var a = 0;
                 int costAddress = int.Parse(oraComm.Parameters["costumer_address"].Value.ToString());
              
              
              oraconn.Dispose();
          }

  Someone can tell me why the varchar2 reurned value gives me the following issue???

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

  
  Can't go ahead....

• • Reply
  

  pasluc7469

  0 Points

  12 Posts

  Re: ora numeric or value error: character string buffer too small

  Feb 26, 2013 02:22 PM|LINK

  Just soved!!!!!

  this issue happens when, when adding parameter into OracleCommand class, you do not specify the size property.

  Here is how i solved:

  Oracle.DataAccess.Client.OracleParameter orapar = new Oracle.DataAccess.Client.OracleParameter();
              orapar.Size = 500;
              orapar.ParameterName = "costumer_address";
              orapar.Direction = ParameterDirection.ReturnValue;
              oraComm.Parameters.Add(orapar);

  
  I hope this can be useful.

  • Marked as answer by Young Yang - MSFT on Mar 11, 2013 01:54 AM