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.

Typo? You have to declare `:objectId` parameter (when `objectName` has been declared *twice*) — Dmitry Bychenko, Jan 24 '17 at 20:40
Yes, thanks, that's a typo I've made while typing my question. It isn't in the source. — Aleksei Omelaienko, Jan 24 '17 at 20:51
@0xdb And you, kind sir, have my sincerest gratitude :) It'd be nice if you wrote that as an answer. — Aleksei Omelaienko, Jan 24 '17 at 21:40
i've resolved simply with a space before "returning" ... VALUES(:objectName) RETURNING — 2GDev, Apr 03 '20 at 18:25

0xdb · Accepted Answer · 2018-01-20T10:14:19.320

4

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

edited Jan 20 '18 at 10:14

answered Jan 24 '17 at 22:06

0xdb

3,539
1
21
37

It'd be nice to add that ODP.NET still allows to do just so, i.e. write something like `"BEGIN\nINSERT INTO objects(name)VALUES('somename')RETURNING id INTO :objectId;\nEND;"` and get returned value back from an output parameter. – Aleksei Omelaienko Jan 24 '17 at 22:16

score 2 · Answer 2 · answered Apr 26 '19 at 16:29

I had the same problem too and initially used @0xdb solution and it worked. Then, I found that the ParameterDirection should be ReturnValue and not Output and everything worked successfully.

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.ReturnValue);
    command.ExecuteNonQuery ();
}

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

2 Answers2