Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


CREATE TABLE UDA_DATA 
( 
  uda VARCHAR2(20), 
  value_text VARCHAR2(4000) 
);
begin
  Insert into UDA_DATA values('Material_ID','PBL000129 PBL000132 PBL000130 PBL000131 PBL000133'); 
  Insert into UDA_DATA values('Material_ID','PBL0001341 PBL0001381 PBL0001351 PBL0001361 PBL0001371'); 
  Insert into UDA_DATA values('Material_ID','PBL0001999'); 
  Insert into UDA_DATA values('Material_ID','PBL000123 PBL000456 PBL000789 PBL000ABC'); 
  Insert into UDA_DATA values('Material_ID','PBL000123 PBL000456 PBL0007890 PBL000DEF'); 
  Insert into UDA_DATA values('Material_ID','PBL000123 PBL000456 PBL00078901 PBL000GHI'); 
  Insert into UDA_DATA values('Material_ID','123456789012345678901234567890'); 
  --Insert into UDA_DATA values('Material_ID','1234567890123456789012345678901'); 
end;
/
1 rows affected

with rcte (rn, uda, value, chunk_num, value_text) as (
  select rownum,
    uda,
    substr(trim(value_text), 1, instr(substr(value_text || ' ', 1, 31), ' ', -1) - 1),
    1,
    trim(substr(trim(value_text), instr(substr(value_text || ' ', 1, 31), ' ', -1) + 1))
  from uda_data
  union all
  select rn,
    uda,
    substr(value_text, 1, instr(substr(value_text || ' ', 1, 31), ' ', -1) - 1),
    chunk_num + 1,
    trim(substr(value_text, instr(substr(value_text || ' ', 1, 31), ' ', -1) + 1))
  from rcte
  where value_text is not null
)
select uda || rn || '/' || chunk_num as uda, value
from rcte
order by rn, chunk_num;


UDA
VALUE


Material_ID1/1
PBL000129 PBL000132 PBL000130

Material_ID1/2
PBL000131 PBL000133

Material_ID2/1
PBL0001341 PBL0001381

Material_ID2/2
PBL0001351 PBL0001361

Material_ID2/3
PBL0001371

Material_ID3/1
PBL0001999

Material_ID4/1
PBL000123 PBL000456 PBL000789

Material_ID4/2
PBL000ABC

Material_ID5/1
PBL000123 PBL000456 PBL0007890

Material_ID5/2
PBL000DEF

Material_ID6/1
PBL000123 PBL000456

Material_ID6/2
PBL00078901 PBL000GHI

Material_ID7/1
123456789012345678901234567890

UDA	VALUE
Material_ID1/1	PBL000129 PBL000132 PBL000130
Material_ID1/2	PBL000131 PBL000133
Material_ID2/1	PBL0001341 PBL0001381
Material_ID2/2	PBL0001351 PBL0001361
Material_ID2/3	PBL0001371
Material_ID3/1	PBL0001999
Material_ID4/1	PBL000123 PBL000456 PBL000789
Material_ID4/2	PBL000ABC
Material_ID5/1	PBL000123 PBL000456 PBL0007890
Material_ID5/2	PBL000DEF
Material_ID6/1	PBL000123 PBL000456
Material_ID6/2	PBL00078901 PBL000GHI
Material_ID7/1	123456789012345678901234567890