Hi, Connor
I'm afraid you've been lucky with your test data:
v varchar2(32000) := '123.45,456.56,678.78,';
Each "element" ('123.45,' and '456.56,' and '678.78,') is exactly 7 characters (including the comma delimiter.) You read chunks of size 32767 which happens to be divisible by 7. So you never get numbers that are "split" between chunks, so your "leftover" handling was never tested ;-)
I run your test exactly as is, except for one line changed:
v varchar2(32000) := '123.456,456.567,678.789,';
Here each "element" is 8 characters. 32767 is not divisible by 8.
After running the test with the slightly changed test data, I run a query on the GTT:
select * from gtt
where n not in (123.456, 456.567, 678.789)
/
N
----------
7
89
456
.567
8.789
23.456
9
56
567
.789
3.456
56.567
12 rows selected.
That's wrong data from places where a number was split in seperate chunks and the leftover handling failed.
Let's look at the code:
SQL> declare
2 l_big_chunk varchar2(32767);
3 l_leftover varchar2(200);
4 l_offset pls_integer;
5 l_len pls_integer;
6
7 type t_rows is table of number index by pls_integer;
8 l_rows t_rows;
9
10 l_comma pls_integer;
11 begin
12 for i in ( select rownum r, c from t ) loop
13 l_offset := 1;
14
15 loop
16 l_big_chunk := l_leftover || dbms_lob.substr(i.c,32767,l_offset);
17 l_len := length(l_big_chunk);
18 l_offset := l_offset + l_len;
19
20 dbms_application_info.set_client_info(i.r||'-'||l_offset);
21
22 loop
23 l_comma := instr(l_big_chunk,',');
24 exit when l_comma = 0 or l_big_chunk is null;
25 if l_comma != 0 then
26 l_rows(l_rows.count+1) := to_number(substr(l_big_chunk,1,l_comma-1));
27 l_big_chunk := substr(l_big_chunk,l_comma+1);
28 else
29 l_leftover := l_big_chunk;
30 end if;
31 end loop;
32 exit when l_len < 32767;
33 end loop;
34
35 end loop;
36 forall i in 1 .. l_rows.count
37 insert into gtt values ( l_rows(i) ) ;
38
39 dbms_output.put_line('rows = '||sql%rowcount);
40 commit;
41
42 end;
43 /
Line 24 exits the loop if no comma is found (l_comma = 0), so you never get to line 29. Therefore l_leftover is never filled with the leftover characters of l_big_chunk. Leftover characters just dissappear.
If that is fixed so l_leftover actually gets leftover characters, then line 16 will try to put more characters in l_big_chunk than there is room for and raise exception.
So here's a take on a quick fix:
declare
l_big_chunk varchar2(32767);
l_leftover varchar2(200);
l_offset pls_integer;
l_len pls_integer;
type t_rows is table of number index by pls_integer;
l_rows t_rows;
l_comma pls_integer;
begin
for i in ( select rownum r, c from t ) loop
l_offset := 1;
loop
l_big_chunk := dbms_lob.substr(i.c,32700,l_offset);
l_len := length(l_big_chunk);
l_offset := l_offset + l_len;
l_big_chunk := l_leftover || l_big_chunk;
dbms_application_info.set_client_info(i.r||'-'||l_offset);
loop
l_comma := instr(l_big_chunk,',');
exit when l_comma = 0 or l_big_chunk is null;
l_rows(l_rows.count+1) := to_number(substr(l_big_chunk,1,l_comma-1));
l_big_chunk := substr(l_big_chunk,l_comma+1);
end loop;
l_leftover := l_big_chunk;
exit when l_len < 32700;
end loop;
end loop;
forall i in 1 .. l_rows.count
insert into gtt values ( l_rows(i) ) ;
dbms_output.put_line('rows = '||sql%rowcount);
commit;
end;
/
Could probably be shined up quite a bit, but as quick fix I think it works ;-)