Skip to Main Content
  • Questions
  • ORA-22922: nonexistent LOB value when parallel merging CLOB data

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Kerry.

Asked: June 24, 2020 - 8:45 pm UTC

Answered by: Chris Saxon - Last updated: July 08, 2020 - 1:44 pm UTC

Category: SQL - Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Viewed 100+ times

You Asked

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?


with LiveSQL Test Case:

and we said...

This question has been a bit of a journey!

First up, I can reproduce this on 12.2, but not 19c. But run this on 19c, get the plan and you'll find this line at the bottom:

- PDML disabled because function is not pure and not declared parallel enabled

You need to add the parallel_enable clause to the function:

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


Do that on 12.2 and the statement and the merge runs for me without error. But the plan now has this note:

- PDML disabled because table has one or more lob columns

As the docs say:

Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/types-parallelism.html#GUID-6626C70C-876C-47A4-8C01-9B66574062D8

So if you want to parallelize the whole statement, it needs a bit of a rethink. I suggest looking into DIY parallelism https://blogs.oracle.com/oraclemagazine/on-working-in-parallel

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.