CODEWITHSUNDEEP
c#sqloracle-database
Anoop
Anoop

Reputation: 15

How to use multiple select queries inside c# by oracle

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

Upvotes: 1

Views: 1440

Answers (2)

Prakash Mhasavekar
Prakash Mhasavekar

Reputation: 592

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;")

Upvotes: 1

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186688

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

Upvotes: 1

Related Questions