Exception calling Stored Procedure with ExecuteArray

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Nihimon
Posts: 4
Joined: Fri 03 Mar 2017 18:09

Exception calling Stored Procedure with ExecuteArray

Post by Nihimon » Tue 23 Jun 2020 16:32

We were previously able to use command.ExecuteArray(n) when the CommandType is StoredProcedure, but this appears to no longer function. Now, when trying to call ExecuteArray, we get the Exception: ORA-28714: OCI_BATCH_ERRORS or OCI_RETURN_ROW_COUNT_ARRAY mode can only be specified for INSERT, UPDATE, DELETE or MERGE statement.

Can anyone help me figure out what's going wrong here?

Here's the database configuration: Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

Code: Select all

drop table lv_testing.test_details;
drop table lv_testing.test_batches;
create table lv_testing.test_batches (
	batch_id varchar2(36) --primary key
);
create table lv_testing.test_details (
	detail_id varchar2(36), --primary key,
	batch_id varchar2(36), --references lv_testing.test_batches(batch_id),
	detail varchar2(240)
);
create or replace procedure lv_testing.write_detail (
	in_batch_id in varchar2,
	in_detail_id in varchar2,
	in_detail in varchar2
) is
begin
	-- Ensure batch header exists.
	insert into lv_testing.test_batches (
			batch_id
		)
		select in_batch_id
			from dual
				left join lv_testing.test_batches b
					on b.batch_id = in_batch_id
			where b.batch_id is null
	;
	insert into lv_testing.test_details (
			detail_id,
			batch_id,
			detail
		)
		values (
			in_detail_id,
			in_batch_id,
			in_detail
		)
	;
end;
/
Here's the Unit Test:

Code: Select all

		public void Devart_Works()
		{
			var connection = new OracleConnection($"user id = {User}; password = {Password}; data source = {DataSource}; Enlist = false")
			{
				Unicode = false,
				PassParametersByName = true,
			};
			connection.Open();
			var transaction = connection.BeginTransaction();

			try
			{
				var command = new OracleCommand("LV_TESTING.WRITE_DETAIL", CommandType.StoredProcedure)
				{
					Connection = connection,
					PassParametersByName = true,
				};
				command.Parameters.Add(new OracleParameter("IN_BATCH_ID", BatchId));
				command.Parameters.Add(new OracleParameter("IN_DETAIL_ID", AlphaId));
				command.Parameters.Add(new OracleParameter("IN_DETAIL", "Alpha"));
				command.ExecuteNonQuery();

				command = new OracleCommand("LV_TESTING.WRITE_DETAIL", CommandType.StoredProcedure)
				{
					Connection = connection,
					PassParametersByName = true,
				};
				command.Parameters.Add(new OracleParameter("IN_BATCH_ID", Enumerable.Repeat(BatchId, 2).ToArray()));
				command.Parameters.Add(new OracleParameter("IN_DETAIL_ID", new string[] { BetaId1, BetaId2, }));
				command.Parameters.Add(new OracleParameter("IN_DETAIL", new string[] { "Beta One", "Beta Two", }));
				command.ExecuteArray(2);

				transaction.Commit();
			}
			catch (Exception ex)
			{
				transaction.Rollback();
				throw ex;
			}
			finally
			{
				connection.Close();
			}
		}

Nihimon
Posts: 4
Joined: Fri 03 Mar 2017 18:09

Re: Exception calling Stored Procedure with ExecuteArray

Post by Nihimon » Wed 24 Jun 2020 18:57

I discovered that this was due to Oracle Instant Client version (19_5). Once switching back to version (12_2), we were able to call ExecuteArray again, without getting the OCI_BATCH_ERRORS exception.

Shalex
Site Admin
Posts: 8981
Joined: Thu 14 Aug 2008 12:44

Re: Exception calling Stored Procedure with ExecuteArray

Post by Shalex » Mon 13 Jul 2020 14:31

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

Shalex
Site Admin
Posts: 8981
Joined: Thu 14 Aug 2008 12:44

Re: Exception calling Stored Procedure with ExecuteArray

Post by Shalex » Fri 31 Jul 2020 18:42

The bug with calling stored procedure using OracleCommand.ExecuteArray() via Oracle Client v19.5 is fixed: viewtopic.php?f=1&t=41450.

Post Reply