0

I created the following procedure:

create or replace procedure ps_medical (
   v_id in number,
   v_name out varchar2,
   v_description out varchar2
) is
begin
select name, description 
  into v_name, v_description 
  from medical_img 
  where id = v_id;
end;

It rises Oracle ORA-06502 numeric or value error. I want to display v_name and v_description.

My table structure:

ID            NOT NULL NUMBER                   
NAME          VARCHAR2(255)            
DESCRIPTION   VARCHAR2(255)

In Sql Developer it works fine. I'm trying to use it in a c# form and there the error rises:

try
{
    con.Open();
}
catch (OracleException ex)
{
    MessageBox.Show(ex.Message);
}

OracleCommand cmd = new OracleCommand("ps_medical", con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("v_id", OracleDbType.Int32);
cmd.Parameters.Add("v_name", OracleDbType.Varchar2);
cmd.Parameters.Add("v_description", OracleDbType.Varchar2);

cmd.Parameters[0].Direction = ParameterDirection.Input;
cmd.Parameters[1].Direction = ParameterDirection.Output;
cmd.Parameters[2].Direction = ParameterDirection.Output;

cmd.Parameters[0].Value = Convert.ToInt32(tb_id.Text);

try
{
    cmd.ExecuteScalar();
    lb_med.Text = Convert.ToString(cmd.Parameters[1].Value);
    tb_desc.Text = Convert.ToString(cmd.Parameters[2].Value);
}
catch (OracleException ex)
{
    MessageBox.Show(ex.Message);
}
con.Close();
5
  • 1
    If you're getting an error ... then you definitely want to FIX the error! Q: Where exactly is the error occurring? When you compile your stored procedure? When some application invokes it? Please update your post, showing exactly where the error occurs (including code), and copy/paste the (full!) error text? Commented Nov 20, 2020 at 22:05
  • In sql developer it works fine. I'm trying to use it in a c# form and there it rises (I displayed the error text in a MessageBox). Commented Nov 20, 2020 at 22:09
  • 1
    Then there is a sizing/type issue in your C# code. Please edit the question and show that. Commented Nov 20, 2020 at 22:10
  • I edited the question. Commented Nov 20, 2020 at 22:17
  • Does this answer your question? ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception from C# code Commented Nov 21, 2020 at 0:24

2 Answers 2

1

I added the size and now it works.

cmd.Parameters.Add("v_name", OracleDbType.Varchar2, 255);

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

Comments

0

If you look here, Oracle NUMBERcorresponds to .Net OracleDbType.Decimal. Please see if changing to "Decimal" resolves the problem.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.