Tuesday, March 11, 2008

ORA-06502 error occurred while calling stored procedure from C#

Here is a Oracle stored procedure.

create or replace procedure GETVPNOFROMPNO(p_pno in varchar2, ret out varchar2) is
r SYS_REFCURSOR;
npno1 varchar2(255);
retval varchar2(255);
begin
retval:= p_pno;
open r for
select npno from parts_for_update_npno where parts_for_update_npno.pno= p_pno and npno is not null;
fetch r into npno1;
if r%NOTFOUND then
ret:= retval;
return;
end if;
retval:= p_pno||' /'||npno1;
ret:= retval;
end GETVPNOFROMPNO;

As you see, there is no need of REF CURSOR. I declared it just for further use.
And here is a function that calls the stored procedure above.

public static string getVPNOfromPNO(OracleConnection c, string pno)
{
OracleCommand cmd = null;
try
{
c.Open();
cmd = c.CreateCommand();
cmd.CommandText = "GETVPNOFROMPNO";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("p_pno", OracleType.VarChar);
cmd.Parameters["p_pno"].Value = pno;
cmd.Parameters["p_pno"].Direction = ParameterDirection.Input;

cmd.Parameters.Add("ret", OracleType.VarChar);
cmd.Parameters["ret"].Value = "";
cmd.Parameters["ret"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();
c.Close();
//MessageBox.Show("Suces");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Program.setStatus("Error while getting ...!", ex);
}
return cmd.Parameters["ret"].Value.ToString();
}

Do you think there is any error? I thought not, but there was.
While executing, this code throws "ORA-06502: numeric or value error: character string buffer too small" exception.

The error is corrected by changing cmd.Parameters["ret"].Value = ""; to cmd.Parameters["ret"].Value = "__________________";

Why? I used parameter for getting output value. The too small string buffer in the error description is cmd.Parameters["ret"].Value that is length of zero. After setting enough space for this variable, it was OK. If i used oracle function, it would be OK without any problem.

2 comments:

Bazzard said...
This comment has been removed by the author.
Bazzard said...

I'm not sure. I allways put parameters in commandtext.
cmd.CommandText = "GETVPNOFROMPNO(\"p_pno\", \"ret\")";