Hi, I have an issue performing a parallel merge of clob data.

I am developing a new process for for part of the solution I am preparing data set for use case testing with randomized data of various data type. I use a function to create a CLOB of randomized size:

create or replace function RANDOM_CLOB
( min_length number
, max_length number
, muliplier number default 1
) return clob
is 
  l_clob clob := empty_clob();
begin  
  for i in 1 .. trunc(dbms_random.value(round(min_length/muliplier,0), round(max_length/muliplier,0)),0)
  loop
    l_clob := l_clob || dbms_random.string('A', muliplier);
  end loop;
  return l_clob;
end;
/

This works fine. To prepare my first three test cases I create an initial data set for UC1, then a data set with 28 records randomly removed for UC2. For US3, I update 25 records with new randomized varchar, numeric and CLOB data. This happens with a merge statement and throws the error :

ORA-12801: error signaled in parallel query server P008
ORA-22922: nonexistent LOB value

The script I use below is:

/* PREPARE TEST CASE ================================================================================================================================= */
/* Prepare Test Case 1 - 123 inserts */
create table reg3_test_case_1
( reg3_test_nk        varchar2(30)
, col_txt1            varchar2(100)
, col_txt2            varchar2(100)
, col_clob            clob
, col_num             number
, col_date            date
, constraint reg3_test_case_1_u01 unique (reg3_test_nk) enable
) 
parallel 
nologging;

insert into reg3_test_case_1 
( reg3_test_nk
, col_txt1
, col_txt2
, col_clob
, col_num
, col_date
)
select 
  'NK'|| to_char(level,'0000000') as reg3_test_nk
, 'Test 1 - Insert record '|| to_char(level,'0000000')
, DBMS_RANDOM.STRING('A', DBMS_RANDOM.value( 1,100 ))
, random_clob(32767,500000,100)
, round(DBMS_RANDOM.value( 100,10000 ),0)
, sysdate
from dual 
connect by level <= 123;
commit;

/* Prepare Test Case 2 - 28 deletes */
create table reg3_test_case_2 parallel nologging
as 
select * 
from reg3_test_case_1
where reg3_test_nk in
  ( select reg3_test_nk from (select reg3_test_nk from reg3_test_case_1 order by dbms_random.value) where rownum <= 95);

/* Prepare Test Case 3 - 25 updates */
create table reg3_test_case_3 parallel nologging
as select *
from reg3_test_case_2;

merge into reg3_test_case_3 t
using 
( select 
    reg3_test_nk
  , 'Update ' || to_char(rownum,'00') as COL_TXT1
  , DBMS_RANDOM.STRING('P', DBMS_RANDOM.value( 1,100 )) as COL_TXT2
  , random_clob(32767,100000,1000) as COL_CLOB
  , rownum as COL_NUM
  , sysdate as COL_DATE
  from
  ( select *
    from reg3_test_case_3 t
    order by dbms_random.value
  ) where rownum <= 25
) s
on (s.reg3_test_nk = t.reg3_test_nk)
when matched then
update
set t.COL_TXT1 = s.COL_TXT1
  , t.COL_TXT2 = s.COL_TXT2
  , t.COL_CLOB = s.COL_CLOB
  , t.COL_NUM = s.COL_NUM
  , t.COL_DATE = s.COL_DATE
;
commit;

I have similar scripts on LiveSQL but for storage considerations I had to reduce the size of the CLOB entry. On that system it works but with the values above I get the
ORA-12801: error signaled in parallel query server P008
ORA-22922: nonexistent LOB value

errors.

Curiously, I found that if I run the merge with the /*+ noparallel *? clause in the select then it works.

merge into reg3_test_case_3 t
using 
( select /*+ noparallel */
    reg3_test_nk
  , 'Update ' || to_char(rownum,'00') as COL_TXT1
  , DBMS_RANDOM.STRING('P', DBMS_RANDOM.value( 1,100 )) as COL_TXT2
  , random_clob(32767,100000,1000) as COL_CLOB
  , rownum as COL_NUM
  , sysdate as COL_DATE
  from
  ( select *
    from reg3_test_case_3 t
    order by dbms_random.value
  ) where rownum <= 25
) s
on (s.reg3_test_nk = t.reg3_test_nk)
when matched then
update
set t.COL_TXT1 = s.COL_TXT1
  , t.COL_TXT2 = s.COL_TXT2
  , t.COL_CLOB = s.COL_CLOB
  , t.COL_NUM = s.COL_NUM
  , t.COL_DATE = s.COL_DATE
;

These are small sample sets of test data. Ultimately I will be pushing this to millions or rows and GBs of storage for testing our new process. Can you explain what might be causing the issue of ORA-22922: nonexistent LOB value when it comes to the parallel selection of data for use inside a merge statement and how to solve it?

create or replace function RANDOM_CLOB
( min_length number
, max_length number
, muliplier number default 1
) return clob parallel_enable 
is 
  l_clob clob := empty_clob();
begin  
  for i in 1 .. trunc(dbms_random.value(round(min_length/muliplier,0), round(max_length/muliplier,0)),0)
  loop
    l_clob := l_clob || dbms_random.string('A', muliplier);
  end loop;
  return l_clob;
end;
/