'ORA-00933: SQL command not properly ended' when executing multiple Select statements in C#

Question

I want to retrieve data from two Select statements in the following piece of code and I get

Oracle.DataAccess.Client.OracleException: 'ORA-00933: SQL command not properly ended'

exception.

Edit: I actually replaced the queries with these: "Select id from T_penalty_order; Select id from T_payment;" and I still get the same error

"Oracle.DataAccess.Client.OracleException: 'ORA-00933: SQL command not properly ended'

using (var connection = new OracleConnection(connectionString))
        using (var command = connection.CreateCommand())
        {
            connection.Open();
            command.CommandText = "select id from t_penalty_order where protokol_no = :invoiceNumber; select A.NAME, A.SURNAME , A.FATHER_NAME, P.PROTOKOL_NO,  P.PROTOKOL_TARIHI , P.PENALTY_FINE, A.FIN, PA.AMOUNT, P.LOCATION_DESCRIPTION, P.QARAR_TARIHI , e.name as \"KANUN\", P.IS_PAID from t_penalty_order p JOIN t_applicant a on p.applicant_id = a.id JOIN t_payment pa on p.id = pa.penalty_order_id JOIN t_penalty_order_kanun_maddesi km ON KM.PENALTY_ORDER_ID = p.id JOIN t_enum_value e ON km.kanun_maddesi_enum = e.id where km.kanun_maddesi_enum = e.id and p.protokol_no = :invoiceNumber group by a.name, a.surname, A.FATHER_NAME, P.PROTOKOL_NO,  P. PROTOKOL_TARIHI , P.PENALTY_FINE, A.FIN, PA.AMOUNT, P.LOCATION_DESCRIPTION, P.QARAR_TARIHI ,  e.name, P.IS_PAID;";

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


            using (var reader = command.ExecuteReader())
            {
                do
                {
                    while (reader.Read())
                    {
                        generalInfoResponse.account = new Account();
                        generalInfoResponse.account = null;
                        generalInfoResponse.invoice = new Invoice[1];
                        generalInfoResponse.invoice = null;

                        generalInfoResponse.response = new Response();
                        generalInfoResponse.response.code = 111;
                        generalInfoResponse.response.message = reader.GetInt32(0).ToString();
                    }
                    //Console.WriteLine("--next command--");
                } while (reader.NextResult());

            }
        }

`P. PROTOKOL_TARIHI` - please, note, ` ` (space) between `.` and `P` — Dmitry Bychenko, Jun 20 '19 at 13:30
Possible duplicate of [Multiple Result Sets with Oracle](https://stackoverflow.com/questions/51249342/multiple-result-sets-with-oracle) — JotaBe, Jun 20 '19 at 13:35
There is no space between ```P``` and ```.``` in P.PROTOKOL_TARIHI. — gunel, Jun 20 '19 at 13:40

Dmitry Bychenko · Answer 1 · 2019-06-21T07:25:43.693

Please, format out your queries, keep them being readable, and you can easily see many pesky syntax errors:

command.CommandText = 
   @"select id 
       from t_penalty_order 
      where protokol_no = :invoiceNumber; -- <- Do you really want to perform next query?

     select A.NAME, 
            A.SURNAME, 
            A.FATHER_NAME, 
            P.PROTOKOL_NO, 
            P.PROTOKOL_TARIHI, 
            P.PENALTY_FINE, 
            A.FIN, 
            PA.AMOUNT, 
            P.LOCATION_DESCRIPTION, 
            P.QARAR_TARIHI, 
            e.name as ""KANUN"", -- <- double quots in case of verbatim strings @"..." 
            P.IS_PAID 
       from t_penalty_order                p 
       join t_applicant                    a ON p.applicant_id = a.id 
       join t_payment                     pa ON p.id = pa.penalty_order_id  
       join t_penalty_order_kanun_maddesi km ON KM.PENALTY_ORDER_ID = p.id 
       join t_enum_value                   e ON km.kanun_maddesi_enum = e.id 
      where km.kanun_maddesi_enum = e.id 
        and p.protokol_no = :invoiceNumber 
   group by a.name, 
            a.surname, 
            A.FATHER_NAME, 
            P.PROTOKOL_NO,  
            P.PROTOKOL_TARIHI, -- <- Not P. PROTOKOL_TARIHI
            P.PENALTY_FINE, 
            A.FIN, 
            PA.AMOUNT, 
            P.LOCATION_DESCRIPTION, 
            P.QARAR_TARIHI,  
            e.name, 
            P.IS_PAID;";

Edit: In case you insist on executing two queries in one go, you should do it via anonymous block in Oracle:

command.CommandText = 
 @"begin -- please, note begin..end anonymous block 
       open :prm_query1 for 
     select id 
       from t_penalty_order 
      where protokol_no = :invoiceNumber; 

       open :prm_query2 for     
     select A.NAME, 
            A.SURNAME, 
            A.FATHER_NAME, 
            P.PROTOKOL_NO, 
            P.PROTOKOL_TARIHI, 
            P.PENALTY_FINE, 
            A.FIN, 
            PA.AMOUNT, 
            P.LOCATION_DESCRIPTION, 
            P.QARAR_TARIHI, 
            e.name as ""KANUN"", 
            P.IS_PAID 
       from t_penalty_order                p 
       join t_applicant                    a ON p.applicant_id = a.id 
       join t_payment                     pa ON p.id = pa.penalty_order_id  
       join t_penalty_order_kanun_maddesi km ON KM.PENALTY_ORDER_ID = p.id 
       join t_enum_value                   e ON km.kanun_maddesi_enum = e.id 
      where km.kanun_maddesi_enum = e.id 
        and p.protokol_no = :invoiceNumber 
   group by a.name, 
            a.surname, 
            A.FATHER_NAME, 
            P.PROTOKOL_NO,  
            P.PROTOKOL_TARIHI, 
            P.PENALTY_FINE, 
            A.FIN, 
            PA.AMOUNT, 
            P.LOCATION_DESCRIPTION, 
            P.QARAR_TARIHI,  
            e.name, 
            P.IS_PAID;
   end;";

Do not forget to declare :prm_query1 and :prm_query2

command.Parameters.Add(":prm_query1", OracleDbType.RefCursor).Direction = 
  ParameterDirection.Output;

command.Parameters.Add(":prm_query2", OracleDbType.RefCursor).Direction =
  ParameterDirection.Output;

When executing via ExecuteReader() we should read each cursor with a help of NextResult():

      using (var reader = command.ExecuteReader()) {
        int queryIndex = 1; 

        do {
          while (reader.Read()) {
            //TODO: Read values here
            // Convert.ToString(reader[0]);
          }

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

I actually replaced the queries with these: "Select id from T_penalty_order; Select id from T_payment;" and I still get the same error "Oracle.DataAccess.Client.OracleException: 'ORA-00933: SQL command not properly ended' — gunel, Jun 21 '19 at 06:22
@gunel: I see; if you really want to open *both* sqls in *one* go, you have to do it (in general case) via anonymous block with a help of ref cursors. Please, see my edit. — Dmitry Bychenko, Jun 21 '19 at 07:29

'ORA-00933: SQL command not properly ended' when executing multiple Select statements in C#

1 Answers1