How to use multiple select queries inside c# by oracle

Question

I need to select multiple select queries inside the C# code block. when I use it, I am getting an error such as an invalid character. I got that it is an error by using a semicolon inside the queries, Is there any solution for selecting multiple select queries?

I am using Oracle.ManagedDataAccess ExecuteReader for executing the query.

Query :

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

I am getting this error as

Oracle.ManagedDataAccess.Client.OracleException: 'ORA-00911: invalid character

If I removed the semicolon then the error changes as

statement are not terminated properly

Dmitrii Bychenko · Accepted Answer · 2019-09-04 13:23:03Z

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());
}

Prakash Mhasavekar · Accepted Answer · 2019-09-04 19:00:39Z

1

Use begin And end to use multiple queries in string

string query = new OracleCommand(@"
        begin
        select COUNT(*) POCount from BISC_PO_DETAIL where TXN_DATE = :today;
      select  COUNT(*) from BISC_ASN_DETAIL where TXN_DATE = :today;
        end;")

edited Sep 4, 2019 at 19:00

answered Sep 4, 2019 at 13:01

Prakash Mhasavekar

5921 gold badge5 silver badges16 bronze badges

2 Comments

Dmitrii Bychenko Over a year ago

1. :today instead of @today 2. Second query COUNT(*) from BISC_ASN_DETAIL where TXN_DATE = @today doesn't compile (you've omitted select)

Prakash Mhasavekar Over a year ago

My mistake. Answered using mobile phone. Missed something while copying above query in answer

Collectives™ on Stack Overflow

How to use multiple select queries inside c# by oracle

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related