In my 23c free Instance I try to create a function, sometimes at recompilation of this function, I get an OERI [17285], [0x7F8DF3E65550], [1], [0x06534EFE0] - but at 2nd try I can compile the function without issues.
I will attach the tracefiles to this post.
The function is quite simple:
create or replace FUNCTION LOB_LINEs
(
IN_CLOB IN CLOB
) RETURN sys.DBMS_DEBUG_VC2COLL
PIPELINED AS
offset number := 1;
amount number := 32767;
eff_size number := 0;
len number := dbms_lob.getlength(IN_CLOB);
lc_buffer varchar2(32767);
i pls_integer := 1;
ll_ret varchar2(10);
BEGIN
-- a quite "stupid" meathod to make my funktion more visible.
select 'LOB_LINEs' into ll_ret; -- from dual
amount := dbms_lob.instr(IN_CLOB, chr(10), offset);
while ( offset < len )
loop
eff_size := least(amount,999); -- DBMS_DEBUG_VC2COLL is table of varchar2(1000)
dbms_output.put_line('off: ' || offset || ' amnt: ' || amount || ' eff: ' || eff_size);
dbms_lob.read(IN_CLOB, eff_size, offset, lc_buffer);
dbms_output.put_line(lc_buffer);
pipe row (lc_buffer);
offset := offset + amount + (case when offset = 1 then 0 else 1);
amount := dbms_lob.instr(IN_CLOB, chr(10), offset) - offset;
i := i + 1;
end loop;
exception
when others then
dbms_output.put_line('LOB_LINEs: Error : '||sqlerrm);
raise;
-- RETURN NULL;
END LOB_LINEs;