Executing a Merge Statement Using WITH Clauses May Fails With: ORA-38910: BATCH ERROR mode is not supported

(Doc ID 1445504.1)

Last updated on AUGUST 30, 2013

Applies to:

PL/SQL - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 30-Aug-2013***

Symptoms


Executing a merge statement using WITH clauses fails with:

ORA-38910: BATCH ERROR mode is not supported for this operation

The same code works in 10.2.0.5.

The following Test Case scenario demonstrates the issue:


SQL> create table test_merge (c1 number, c2 varchar2(30));

Table created.

SQL> insert into test_merge values (1,'no value');

1 row created.

SQL> commit;

Commit complete.

SQL> set serveroutput on
SQL> declare
2 type num_tab is table of number;
3 nt num_tab := num_tab(100,200,300,400,500);
4 dml_errors exception;
5 pragma exception_init(dml_errors,-24381);
6 begin
7 forall i in 1..5 save exceptions
8 merge into test_merge tgt using (
9 with
10 loan_main as (select 'loan_main' lm from dept),
11 loan_asset as (select lm la from loan_main),
12 deals as (select distinct la d from loan_asset),
13 party_main as (select d pm from deals),
14 party_agent as (select d pa from deals)
15 select a.lm col
16 from loan_main a, loan_asset b , party_main c, party_agent pa
17 where a.lm = b.la
18 and c.pm = pa.pa
19 and b.la = c.pm ) src
20 on (tgt.c2 = src.col)
21 when not matched then
22 insert (c2,c1) values (src.col,nt(i));
23 exception
24 when dml_errors then
25 dbms_output.put_line('Failed: '||sqlerrm);
26 FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
27 LOOP
28
29 DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
30 END LOOP;
31 end;
32 /
Failed: ORA-24381: error(s) in array DML
ORA-38910: BATCH ERROR mode is not supported for this operation
ORA-38910: BATCH ERROR mode is not supported for this operation
ORA-38910: BATCH ERROR mode is not supported for this operation
ORA-38910: BATCH ERROR mode is not supported for this operation
ORA-38910: BATCH ERROR mode is not supported for this operation

PL/SQL procedure successfully completed.

Changes

Upgraded the Database to 11g

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms