.net - How to pass boolean parameter to Oracle procedure C#

Question

I have problem to pass boolean parameter to my procedure in Oracle. I get error

ORA-06550: line 1, column 7:
  PLS-00306: wrong number or types of arguments in call to 'LOG_ENTRY'
ORA-06550: line 1, column 7:
  PL/SQL: Statement ignored"

Procedure in Oracle:

log_entry(p_rqserial in integer,
          p_orig in varchar,
          p_type in char,
          p_objname in varchar,
          p_info in varchar,
          p_text in varchar, p_with_commit boolean)

This my code :

    cmd = new Oracle.DataAccess.Client.OracleCommand("Vbank_pkg.vb_log_entry", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("p_rqserial", OracleDbType.Int32).Value = Log_Serial;
    cmd.Parameters.Add("p_orig", OracleDbType.Varchar2).Value = "'" + p_orig + "'";
    cmd.Parameters.Add("p_type", OracleDbType.Char).Value = "'" + p_type + "'";
    cmd.Parameters.Add("p_objname", OracleDbType.Varchar2).Value = "'" + p_objname + "'";
    cmd.Parameters.Add("p_info", OracleDbType.Varchar2).Value = "'" + p_info + "'";
    cmd.Parameters.Add("p_text", OracleDbType.Varchar2).Value = "'" + p_text + "'";
    cmd.Parameters.Add("p_with_commit", OracleDbType.Char).Value =true;
    cmd.ExecuteNonQuery();//Here error

Do any know how to do this? Because I can't find any solution and many people says it's inpossible to pass bool parameter from C# to Oracle but I can't believe there is no way.

Answer 1

Try using BindByName property of OracleCommand as true to force the data provider bind these parameters by name and not just for the index. You do not need to pass the ' as parameters. It is lead with parameters in the ado.net.

ODP.NET does not support Boolean data type. Just use 1 or 0 as a char value to persist it.

For Sample:

cmd = new Oracle.DataAccess.Client.OracleCommand("Vbank_pkg.vb_log_entry", con);

cmd.BindByName = true;
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("p_rqserial", OracleDbType.Int32).Value = Log_Serial;
cmd.Parameters.Add("p_orig", OracleDbType.Varchar2).Value = p_orig;
cmd.Parameters.Add("p_type", OracleDbType.Char).Value = p_type;
cmd.Parameters.Add("p_objname", OracleDbType.Varchar2).Value = p_objname;
cmd.Parameters.Add("p_info", OracleDbType.Varchar2).Value = p_info;
cmd.Parameters.Add("p_text", OracleDbType.Varchar2).Value = p_text;
cmd.Parameters.Add("p_with_commit", OracleDbType.Char).Value = "1";

cmd.ExecuteNonQuery();