C# show custom error from Oracle procedure

Question

create or replace procedure naujas_veiksmai(
vartotojas number,
knyga number,
kiekis number,
grazinta VARCHAR2,
tipas varchar2,
error_msg out varchar2
)

is
begin
insert into veiksmai values(vt_id_seq.nextval,vartotojas,knyga,kiekis,tipas,sysdate,TO_DATE(grazinta,'YYYY-MM-DD'));
exception 
when OTHERS THEN error_msg := 'Irasant ivyko klaida'; 
end;

This is procedure that must return custom error to program.

C# code:

OracleCommand cmd = new OracleCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "naujas_veiksmai";

                    cmd.CommandType = CommandType.StoredProcedure;
                    OdbcParameter param = new OdbcParameter();

                        cmd.Parameters.Add("vartotojas", OracleType.Number).Value = vartotojas;
                        cmd.Parameters.Add("knyga", OracleType.Number).Value = knyga;
                        cmd.Parameters.Add("kiekis", OracleType.Number).Value = kiekis;
                        cmd.Parameters.Add("grazinta", OracleType.VarChar).Value = grazinti;
                        cmd.Parameters.Add("tipas", OracleType.VarChar).Value = tipas;
                        OracleParameter op = new OracleParameter("error_msg", OracleType.VarChar);
                        op.Direction = ParameterDirection.Output;
                        op.Size = 200;
                        cmd.Parameters.Add(op);


                    cmd.ExecuteNonQuery();
                    cmd.Parameters.RemoveAt(0);

Program not showing any errors from oracle, insert was not executed and I know that must be exception.

What is wrong in procedure or in oracle code ?

Grant Winney · Accepted Answer · 2014-04-21 11:47:38Z

2

You've already told the stored procedure that, if any exception is thrown, write a message to the error_msg parameter.

Check the value of that parameter after executing the stored procedure:

cmd.ExecuteNonQuery();

var errorMessage = Convert.ToString(cmd.Parameters["error_msg"].Value);

answered Apr 21, 2014 at 11:47

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

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

Comments

Farhad Jabiyev · Accepted Answer · 2014-04-21 13:07:28Z

1

It is not good idea to add additional out parameter for sending exception message to the back-end.

If you want to see your exception message in try/catch block , then you must use RAISE_APPLICATION_ERROR. The RAISE_APPLICATION_ERROR built-in is used for just a single scenario: if you need to communicate an application-specific error back to the user.

  RAISE_APPLICATION_ERROR (-20003, 'CUSTOM EXCEPTION MESSAGE');

and it will throw exactly same exception in this statement:

try
{
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    string customMessage = ex.Message;
}

Update:

WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR (-20003, 'CUSTOM EXCEPTION MESSAGE');

edited Apr 21, 2014 at 13:07

answered Apr 21, 2014 at 11:44

Farhad Jabiyev

26.9k8 gold badges79 silver badges103 bronze badges

Collectives™ on Stack Overflow

C# show custom error from Oracle procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related