Insert + Select statements in one Query gives ORA-00933: SQL command not properly ended

dapper oracle

Question

I'd like to do a simple INSERT-and-then-SELECT-it-query in one "run" with dapper, roughly like so:

var query = "INSERT INTO Table(Id, Value) Values(1, :value);" +
            "SELECT Id, Value FROM Table WHERE Id = 1;"

Connection.Query<Item>(query, new { value = value});

This just gives me ORA-00933: SQL command not properly ended. If I run the exact same query (in "one run", with the params manually replaced) in Oracle SQL Developer, it runs fine.

If I split this in two, and do this:

var insert = "INSERT INTO Table(Id, Value) Values(1, :value);"
var select = "SELECT Id, Value FROM Table WHERE Id = 1;"

Connection.Execute(insert, new { value = value});
var item = Connection.Query<Item>(select);

...it works fine. I've tried removing the last semicolon (which seemed to be a thing in other questions), with no luck.

I've done this exact same thing with success in another project where I'm using SQL Server.

Using Dapper 1.42.

Can anyone see what's wrong here?

Accepted Answer

Try this. In Insert add "Returning into Clause" and declare output parameters.

  var param = new DynamicParameters();
param.Add(name: "IinsValue", value: value, direction: ParameterDirection.Input);
param.Add(name: "Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
param.Add(name: "Value", dbType: DbType.String, direction: ParameterDirection.Output);
Connection.Execute("INSERT INTO Table(Id, Value) Values(1, :value) returning Id,Values into :id, :value", param );
var Id = param.get<int>("Id");
var Val = param.get<String>("Value");


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why