2

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.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Vladimir Potapov
  • 2,347
  • 7
  • 44
  • 71
  • Remove the quotes, that's one of the main benefits to use prepared statements and bind parameters. – Wernfried Domscheit Jan 13 '15 at 10:46
  • Unbelievable but true: boolean parameters are not supported between C# and Oracle stored procedure. You need to use a Y/N or 0/1 type of parameter instead :-( – Roland Dec 30 '20 at 09:31

2 Answers2

2

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();
Felipe Oriani
  • 37,948
  • 19
  • 131
  • 194
  • 1
    I all ready tried this,it is not working.After all time that i spend on this i ask to change the function in Oracle,and chaned the parameter to integer 0-false and 1-true,it is be much faster :) – Vladimir Potapov Jan 13 '15 at 10:15
  • But i still be very happy to find another solution. – Vladimir Potapov Jan 13 '15 at 10:16
  • Yes, I agree with you, but, unfortunately ODP.NET does not support bool type, so, you have to use 1 or 0. – Felipe Oriani Jan 13 '15 at 10:37
  • When I try using `OracleDbType.Char`, I get the following error: `"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'REGISTER'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored" System.Exception {Oracle.ManagedDataAccess.Client.OracleException}`. I can avoid this in my own stored procedures, but I can't avoid this with Oracle's built in stored procedures without creating "wrappers" to translate. What is odd is that it worked for me with ODP.NET until I just upgraded from 12c1 to current version 12c2. – gridtrak Aug 31 '17 at 19:18
  • This solution doesn't appear to work in Oracle.ManagedDataAccess v 12.1 either. Has anyone figured out how to make this work? I see with gridtrak mentioned, it used to work, but now it doesn't work anymore. – Jay May 31 '18 at 00:01
-1

Consider the following procedure as example

CREATE OR REPLACE PROCEDURE my_procedure
(
   condition NUMBER,...

In C#, add parameter

cmd.Parameters.Add("@condition", OracleDbType.Char).Value = myObject.isCondition ? 1 : 0;
Eduardo Reis
  • 1,691
  • 1
  • 22
  • 45
Cassia
  • 21
  • 1