Below is the test tables and data
--DDL
create table tab_clob_data (Optionid number(10),OptionDescription clob);
/
--DML
declare
v_data clob:='';
begin
for i in 1 .. 10 loop
v_data :=DBMS_RANDOM.string('x',10);
while length(v_data) < 6000 loop
v_data:= v_data ||','|| DBMS_RANDOM.string('x',10);
end loop;
insert into tab_clob_data values (i,v_data);
commit;
end loop;
end;
/
i need to parse the column OptionDescription of the table tab_clob_data into rows
Solution 1 tried with below query it takes too much of time. I have around 800K records in the table tab_clob_data
select
trim(
regexp_substr(OptionDescription, '[^,]+', 1, levels.column_value)
) as OptionDesc,
Optionid
from tab_clob_data,
table(cast(multiset(
select level from dual
connect by level <= length (regexp_replace(OptionDescription, '[^,]+')) + 1
) as sys.OdciNumberList)) levels;
Solution 2 i was trying this gives error
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
SELECT Optionid,
UPPER(TRIM(SUBS))
FROM tab_clob_data
inner join xmltable(
'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
passing OptionDescription as X
columns SUBS clob path '.')
on OptionDescription IS NOT NULL;
Solution 3 i was trying but this way i am loosing data since i doing sub string of the CLOB column
SELECT Optionid,
UPPER(TRIM(SUBS))
FROM tab_clob_data
inner join xmltable(
'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
passing dbms_lob.substr( OptionDescription, 4000, 1 ) as X
columns SUBS varchar2(4000) path '.')
on OptionDescription IS NOT NULL;
is there any way to convert the CLOB data in rows and also not loose any data?