1

I have the following SQL command (simplified) assigned to the var sql:

INSERT ALL
    INTO ORACLETBL (COL_A,COL_B) VALUES ('A','B')
    INTO ORACLETBL (COL_A,COL_B) VALUES ('C','D')
SELECT * FROM dual;

When I do a conn.Execute(sql); in a void method, the command works correctly.

When I do an await conn.ExecuteAsync(sql); in an async Task method, against the exact same SQL command, I get the exception "ORA-00933: SQL command not properly ended".

Is it obvious to anyone what I'm doing wrong?

k3davis
  • 985
  • 12
  • 29

2 Answers2

0

I'm not sure why it throws an exception in one situation but not the other, but it may be related to your using ExecuteAsync when you mean to use QueryAsync.

Execute is not intended for use with selects, but instead returns the number of affected rows when an operation is performed.

In the ExecuteAsync documentation you'll notice they use ExecuteAsync and only receive back the number of rows affected, but they use Query when they want to retrieve actual rows.

This stack post contains a more detailed explanation of the difference between Execute and Query.

David Padrick
  • 26
  • 1
  • 4
  • It's an INSERT statement, not a SELECT (though the Oracle syntax for "insert many" includes a SELECT subquery). Anyway, using QueryAsync throws the same exception. – k3davis May 10 '19 at 17:46
0

Remove the semicolon at the end of the statement.

Edit:

Maybe that's a solution, too.

  string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";

  using (var connection = new SqlConnection("")) {
    connection.Open();

    var affectedRows = await connection.ExecuteAsync(sql,
      new[] {
        new {CustomerName = "John"},
        new {CustomerName = "Andy"},
        new {CustomerName = "Allan"}
      }
    );

  }

Or try 

SELECT 1 FROM DUAL

instead of 

SELECT * FROM DUAL
  • Without the semicolon it doesn't work, even in the synchronous manner. But it behaves strangely (appears to end without doing the operation _or_ throwing an exception). Perhaps there is a debugging issue with this in VS2019. – k3davis May 10 '19 at 23:28
  • This issue turned out to be an idiotic attempt to perform the SQL commands while there was a lock on the table, which caused all kinds of unnecessary rabbit trails. Once the underlying issue was addressed ("restart your computer!") the command worked normally with the semicolon removed; no further changes necessary. – k3davis May 13 '19 at 15:46