Execute Multiple Queries in Single Oracle Command in C#

Execute multiple queries in single Oracle command in C#

In order to execute more than one command put them in begin ... end; block.
And for DDL statements (like create table) run them with execute immediate. This code worked for me:

OracleConnection con = new OracleConnection(connectionString);
con.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText =
    "begin " +
    "  execute immediate 'create table test1(name varchar2(50) not null)';" +
    "  execute immediate 'create table test2(name varchar2(50) not null)';" +
    "end;";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();

More info: Executing SQL Scripts with Oracle.ODP

how to execute multiple oracle query c#

Although you're using names for your parameters, your driver is treating them positionally. You can kind of tell because it's (almost) matching :1 with the name p_cr1 - '1' isn't a valid name. It doesn't complain since it matches positionally - but that means it's trying to use the P_para for :1, and as the type of that is wrong, that explains the error you see.

There may well be a way to change the driver's behaviour, but for now you can just swap the order you bind them - so the binds occur in the same order (position) the variables appear in the query. So:

cmd.Parameters.Add("p_cr1", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
cmd.Parameters.Add(new OracleParameter(":P_para", OracleDbType.Int64)).Value = Convert.ToInt64(Textbox.Text);
cmd.Parameters.Add("p_cr2", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

How to use multiple select queries inside c# by oracle

Since both queries return scalar values, you can combine them as

select (select COUNT(*) from BISC_PO_DETAIL where TXN_DATE = :today) POCount,
       (select COUNT(*) from BISC_ASN_DETAIL where TXN_DATE = :today) ASNCount
  from Dual 

and read POCount and ASNCount fields. Please, note that Oracle uses : for parameters, something like this:

string query = 
  @"select (select COUNT(*) from BISC_PO_DETAIL where TXN_DATE = :today) POCount,
           (select COUNT(*) from BISC_ASN_DETAIL where TXN_DATE = :today) ASNCount
      from Dual";

using (OracleCommand q = new OracleCommand(query, conn)) {
  q.Parameters.Add(":today", OracleDbType.Date);

  q.Parameters[":today"].Value = DateTime.Today;

  using (var reader = q.ExecuteReader()) {
    if (reader.Read()) {
      int POCount = Convert.ToInt32(reader["POCount"]);
      int ASNCount = Convert.ToInt32(reader["ASNCount"]);
      ....
    }
  }
}

Another (more general) possibility is using cursors and NextResult:

string query = 

@"declare
    query1 ref cursor;
    query2 ref cursor;
  begin
    open query1 for select COUNT(*) from BISC_PO_DETAIL where TXN_DATE = :today;

    open query2 for select COUNT(*) from BISC_ASN_DETAIL where TXN_DATE = :today;
  end;";

...

using (var reader = q.ExecuteReader()) {
  int cursorIndex = 0;

  do {
    if (reader.Read()) {
      int value = Convert.ToInt32(reader[0]);

      if (cursorIndex == 0) {
        // value is BISC_PO_DETAIL count
      } 
      else {
        // value is BISC_ASN_DETAIL count
      }
    }

    cursorIndex += 1;
  }
  while (reader.NextResult());
}

Unable to execute multiple Oracle queries in C#

I suggest executing just one query (we don't want to do extra work by querying Oracle twice, fetching data to the workstation then pushing at again to Oracle). As far as I can see from your

"Update t_payment set amount = :amount where penalty_order_id = (select id from t_penalty_order where protokol_no = :invoiceNumber)";

code, you want to update t_payment table by adding some extra money to amount field with condition applied:

update t_payment
   set amount = amount + SOME_EXTRA_MONEY
 where penalty_order_id in (select p.id 
                              from t_penalty_order p
                             where p.protokol_no = :invoiceNumber) 

We have to determine what is SOME_EXTRA_MONEY: we can try to derive it from query

"select amount from t_payment where penalty_order_id = (select id from t_penalty_order where protokol_no = :invoiceNumber)";

So we have (I've added Sum in case we have several records, and that's why should sum them and Nvl in case we have none - in this case extra sum is 0):

update t_payment
   set amount = amount + (select Nvl(Sum(t.amount), 0) 
                            from t_payment t 
                           where t.penalty_order_id in (select p.id 
                                                          from t_penalty_order p
                                                         where p.protokol_no = :invoiceNumber))
 where penalty_order_id in (select p.id 
                              from t_penalty_order p
                             where p.protokol_no = :invoiceNumber)

Time to execute this query:

 using (OracleCommand command2 = new OracleCommand()) {
   command2.CommandText =
     @"update t_payment
          set amount = amount + (select Nvl(Sum(t.amount), 0) 
                                   from t_payment t 
                                  where t.penalty_order_id in (select p.id 
                                                                 from t_penalty_order p
                                                                where p.protokol_no = :invoiceNumber))
        where penalty_order_id in (select p.id 
                                     from t_penalty_order p
                                    where p.protokol_no = :invoiceNumber)";

   command2.Parameters.Add(
     new OracleParameter(@"invoiceNumber", OracleDbType.Varchar2, 255)
   ).Value = request.invoiceNumber;

   command2.ExecuteNonQuery();
 } 

Multiple SQL queries C# using Oracle DB

try
{
    OracleConnection con = new OracleConnection();
    con.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.24)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DEVL)));User Id=aaziz;Password=123211;";
    con.Open();
    string cmdQuery = "Insert into M.person (RED_NO, USED_FLAG) VALUES ('12', '0')";
    OracleCommand cmd = new OracleCommand(cmdQuery);
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();
    cmd.CommandText = "INSERT NEW QUERY HERE";
    cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();
    cmd.CommandText = "INSERT NEW QUERY HERE";
    cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();
    cmd.CommandText = "INSERT NEW QUERY HERE";
    cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();
    cmd.CommandText = "INSERT NEW QUERY HERE";
    cmd.ExecuteNonQuery();

    con.Dispose();
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);

---

Related Topics