Using INSERT...RETURNING clause leads to ORA-12537

Question

The problem is as simple as stated in question topic. I try to run some query using INSERT...RETURNING clause, which raises ORA-12537 exception when I try to execute it. The source is as follows:

using ( OracleCommand command = new OracleCommand () ) {
    command.Connection = connection;
    command.BindByName = true;
    command.CommandText = "INSERT INTO objects(name)VALUES(:objectName)RETURNING id INTO :objectId";
    command.Parameters.Add ( "objectName", OracleDbType.Varchar2, ParameterDirection.Input );
    command.Parameters.Add ( "objectId", OracleDbType.Int64, ParameterDirection.Output );
    command.ExecuteNonQuery ();
}

Execution of the last line leads to OracleException being raised with message ORA-12537: Network Session: End of file. Running same query without RETURNING sub-clause goes obviously smooth.

Answer 1

OracleCommand.CommandText property sets the SQL statement or stored procedure to execute.
The ORA-12537 is an information message only and means that the connection has been closed. This can be caused by a number of reasons, i.a. oracle can't execute the sql statement properly and terminates the session.

Try to execute the statement as pl/sql block instead of in a pure sql context:

command.CommandText = @"
    begin 
        insert into objects(name) values(:objectName) returning id into :objectId; 
    end;";