Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 8b45b0d

Browse filesBrowse files
jgebaljgebal
committed
Fix output length error and output buffer.
When dealing with multibyte strings, the substring limit of 4000 **chars** is not correct. We need to apply a limit of 4000 **bytes** to both VARCHAR2 and to CLOB data types. This means we need to have a function to calculate lenght of CLOB in bytes. This is now added and all the conversions/trimming/string-splitting is done using the string length in **bytes** Additionally, a different approach (with sequences) was introduced into output buffers to avoid the sometime occurring PK violation on the output buffer tables. The new approach may add a but overhead on reading from / saving into buffer, but will definitely address the threat of PK errors. Resolves #1254 Resolves #1128 Replaced all regexp ranges with posix representations to avoid NLS_SORT impact on regex behavior.
1 parent 6589b84 commit 8b45b0d
Copy full SHA for 8b45b0d
Expand file treeCollapse file tree

14 files changed

+149
-54
lines changed

‎source/core/output_buffers/ut_output_buffer_base.tpb

Copy file name to clipboardExpand all lines: source/core/output_buffers/ut_output_buffer_base.tpb
-1Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,6 @@ create or replace type body ut_output_buffer_base is
2424
self.self_type := coalesce(a_self_type,self.self_type);
2525
self.output_id := coalesce(a_output_id, self.output_id, sys_guid());
2626
self.start_date := coalesce(self.start_date, sysdate);
27-
self.last_write_message_id := 0;
2827
select /*+ no_parallel */ count(*) into l_exists from ut_output_buffer_info_tmp where output_id = self.output_id;
2928
if ( l_exists > 0 ) then
3029
update /*+ no_parallel */ ut_output_buffer_info_tmp set start_date = self.start_date where output_id = self.output_id;

‎source/core/output_buffers/ut_output_buffer_base.tps

Copy file name to clipboardExpand all lines: source/core/output_buffers/ut_output_buffer_base.tps
-1Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,6 @@ create or replace type ut_output_buffer_base force authid definer as object(
1919
output_id raw(32),
2020
is_closed number(1,0),
2121
start_date date,
22-
last_write_message_id number(38,0),
2322
lock_handle varchar2(30 byte),
2423
self_type varchar2(250 byte),
2524
member procedure init(self in out nocopy ut_output_buffer_base, a_output_id raw := null, a_self_type varchar2 := null),

‎source/core/output_buffers/ut_output_buffer_tmp.sql

Copy file name to clipboardExpand all lines: source/core/output_buffers/ut_output_buffer_tmp.sql
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,3 +30,4 @@ create table ut_output_buffer_tmp(
3030
) organization index nologging initrans 100
3131
overflow nologging initrans 100;
3232

33+
create sequence ut_output_buffer_tmp_seq cache 20;

‎source/core/output_buffers/ut_output_bulk_buffer.tpb

Copy file name to clipboardExpand all lines: source/core/output_buffers/ut_output_bulk_buffer.tpb
+3-6Lines changed: 3 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -34,9 +34,8 @@ create or replace type body ut_output_bulk_buffer is
3434
a_item_type
3535
);
3636
else
37-
self.last_write_message_id := self.last_write_message_id + 1;
3837
insert /*+ no_parallel */ into ut_output_buffer_tmp(output_id, message_id, text, item_type)
39-
values (self.output_id, self.last_write_message_id, a_text, a_item_type);
38+
values (self.output_id, ut_output_buffer_tmp_seq.nextval, a_text, a_item_type);
4039
end if;
4140
commit;
4241
end if;
@@ -46,10 +45,9 @@ create or replace type body ut_output_bulk_buffer is
4645
pragma autonomous_transaction;
4746
begin
4847
insert /*+ no_parallel */ into ut_output_buffer_tmp(output_id, message_id, text, item_type)
49-
select /*+ no_parallel */ self.output_id, self.last_write_message_id + rownum, t.column_value, a_item_type
48+
select /*+ no_parallel */ self.output_id, ut_output_buffer_tmp_seq.nextval, t.column_value, a_item_type
5049
from table(a_text_list) t
5150
where t.column_value is not null or a_item_type is not null;
52-
self.last_write_message_id := self.last_write_message_id + sql%rowcount;
5351
commit;
5452
end;
5553

@@ -65,9 +63,8 @@ create or replace type body ut_output_bulk_buffer is
6563
a_item_type
6664
);
6765
else
68-
self.last_write_message_id := self.last_write_message_id + 1;
6966
insert /*+ no_parallel */ into ut_output_buffer_tmp(output_id, message_id, text, item_type)
70-
values (self.output_id, self.last_write_message_id, a_text, a_item_type);
67+
values (self.output_id, ut_output_buffer_tmp_seq.nextval, a_text, a_item_type);
7168
end if;
7269
commit;
7370
end if;

‎source/core/output_buffers/ut_output_clob_buffer_tmp.sql

Copy file name to clipboardExpand all lines: source/core/output_buffers/ut_output_clob_buffer_tmp.sql
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,3 +45,5 @@ begin
4545
end;
4646
end;
4747
/
48+
49+
create sequence ut_output_clob_buffer_tmp_seq cache 20;

‎source/core/output_buffers/ut_output_clob_table_buffer.tpb

Copy file name to clipboardExpand all lines: source/core/output_buffers/ut_output_clob_table_buffer.tpb
+5-12Lines changed: 5 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -26,9 +26,8 @@ create or replace type body ut_output_clob_table_buffer is
2626
pragma autonomous_transaction;
2727
begin
2828
if a_text is not null or a_item_type is not null then
29-
self.last_write_message_id := self.last_write_message_id + 1;
3029
insert /*+ no_parallel */ into ut_output_clob_buffer_tmp(output_id, message_id, text, item_type)
31-
values (self.output_id, self.last_write_message_id, a_text, a_item_type);
30+
values (self.output_id, ut_output_clob_buffer_tmp_seq.nextval, a_text, a_item_type);
3231
end if;
3332
commit;
3433
end;
@@ -37,20 +36,18 @@ create or replace type body ut_output_clob_table_buffer is
3736
pragma autonomous_transaction;
3837
begin
3938
insert /*+ no_parallel */ into ut_output_clob_buffer_tmp(output_id, message_id, text, item_type)
40-
select /*+ no_parallel */ self.output_id, self.last_write_message_id + rownum, t.column_value, a_item_type
39+
select /*+ no_parallel */ self.output_id, ut_output_clob_buffer_tmp_seq.nextval, t.column_value, a_item_type
4140
from table(a_text_list) t
4241
where t.column_value is not null or a_item_type is not null;
43-
self.last_write_message_id := self.last_write_message_id + SQL%rowcount;
4442
commit;
4543
end;
4644

4745
overriding member procedure send_clob(self in out nocopy ut_output_clob_table_buffer, a_text clob, a_item_type varchar2 := null) is
4846
pragma autonomous_transaction;
4947
begin
5048
if a_text is not null and a_text != empty_clob() or a_item_type is not null then
51-
self.last_write_message_id := self.last_write_message_id + 1;
5249
insert /*+ no_parallel */ into ut_output_clob_buffer_tmp(output_id, message_id, text, item_type)
53-
values (self.output_id, self.last_write_message_id, a_text, a_item_type);
50+
values (self.output_id, ut_output_clob_buffer_tmp_seq.nextval, a_text, a_item_type);
5451
end if;
5552
commit;
5653
end;
@@ -60,33 +57,29 @@ create or replace type body ut_output_clob_table_buffer is
6057
l_buffer_rowids ut_varchar2_rows;
6158
l_buffer_data ut_output_data_rows;
6259
l_finished_flags ut_integer_list;
63-
l_last_read_message_id integer;
6460
l_already_waited_sec number(10,2) := 0;
6561
l_finished boolean := false;
6662
l_sleep_time number(2,1);
6763
l_lock_status integer;
6864
l_producer_started boolean := false;
6965
l_producer_finished boolean := false;
7066
procedure get_data_from_buffer_table(
71-
a_last_read_message_id in out nocopy integer,
7267
a_buffer_data out nocopy ut_output_data_rows,
7368
a_buffer_rowids out nocopy ut_varchar2_rows,
7469
a_finished_flags out nocopy ut_integer_list
7570
) is
7671
lc_bulk_limit constant integer := 5000;
7772
begin
78-
a_last_read_message_id := coalesce(a_last_read_message_id, 0);
7973
with ordered_buffer as (
8074
select /*+ no_parallel index(a) */ ut_output_data_row(a.text, a.item_type), rowidtochar(a.rowid), is_finished
8175
from ut_output_clob_buffer_tmp a
8276
where a.output_id = self.output_id
83-
and a.message_id <= a_last_read_message_id + lc_bulk_limit
77+
and a.message_id <= (select min(message_id) from ut_output_clob_buffer_tmp o where o.output_id = self.output_id) + lc_bulk_limit
8478
order by a.message_id
8579
)
8680
select /*+ no_parallel */ b.*
8781
bulk collect into a_buffer_data, a_buffer_rowids, a_finished_flags
8882
from ordered_buffer b;
89-
a_last_read_message_id := a_last_read_message_id + a_finished_flags.count;
9083
end;
9184

9285
procedure remove_read_data(a_buffer_rowids ut_varchar2_rows) is
@@ -103,7 +96,7 @@ create or replace type body ut_output_clob_table_buffer is
10396

10497
l_sleep_time := case when l_already_waited_sec >= 1 then 0.5 else 0.1 end;
10598
l_lock_status := self.get_lock_status();
106-
get_data_from_buffer_table( l_last_read_message_id, l_buffer_data, l_buffer_rowids, l_finished_flags );
99+
get_data_from_buffer_table( l_buffer_data, l_buffer_rowids, l_finished_flags );
107100

108101
if l_buffer_data.count > 0 then
109102
l_already_waited_sec := 0;

‎source/core/output_buffers/ut_output_table_buffer.tpb

Copy file name to clipboardExpand all lines: source/core/output_buffers/ut_output_table_buffer.tpb
+10-17Lines changed: 10 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -26,17 +26,16 @@ create or replace type body ut_output_table_buffer is
2626
pragma autonomous_transaction;
2727
begin
2828
if a_text is not null or a_item_type is not null then
29-
if length(a_text) > ut_utils.gc_max_storage_varchar2_len then
29+
if lengthb(a_text) > ut_utils.gc_max_storage_varchar2_len then
3030
self.send_lines(
3131
ut_utils.convert_collection(
3232
ut_utils.clob_to_table(a_text, ut_utils.gc_max_storage_varchar2_len)
3333
),
3434
a_item_type
3535
);
3636
else
37-
self.last_write_message_id := self.last_write_message_id + 1;
3837
insert /*+ no_parallel */ into ut_output_buffer_tmp(output_id, message_id, text, item_type)
39-
values (self.output_id, self.last_write_message_id, a_text, a_item_type);
38+
values (self.output_id, ut_output_buffer_tmp_seq.nextval, a_text, a_item_type);
4039
end if;
4140
commit;
4241
end if;
@@ -46,28 +45,26 @@ create or replace type body ut_output_table_buffer is
4645
pragma autonomous_transaction;
4746
begin
4847
insert /*+ no_parallel */ into ut_output_buffer_tmp(output_id, message_id, text, item_type)
49-
select /*+ no_parallel */ self.output_id, self.last_write_message_id + rownum, t.column_value, a_item_type
48+
select /*+ no_parallel */ self.output_id, ut_output_buffer_tmp_seq.nextval, t.column_value, a_item_type
5049
from table(a_text_list) t
5150
where t.column_value is not null or a_item_type is not null;
52-
self.last_write_message_id := self.last_write_message_id + SQL%rowcount;
5351
commit;
5452
end;
5553

5654
overriding member procedure send_clob(self in out nocopy ut_output_table_buffer, a_text clob, a_item_type varchar2 := null) is
5755
pragma autonomous_transaction;
5856
begin
5957
if a_text is not null and a_text != empty_clob() or a_item_type is not null then
60-
if length(a_text) > ut_utils.gc_max_storage_varchar2_len then
58+
if ut_utils.lengthb_clob(a_text) > ut_utils.gc_max_storage_varchar2_len then
6159
self.send_lines(
6260
ut_utils.convert_collection(
6361
ut_utils.clob_to_table(a_text, ut_utils.gc_max_storage_varchar2_len)
6462
),
6563
a_item_type
6664
);
6765
else
68-
self.last_write_message_id := self.last_write_message_id + 1;
6966
insert /*+ no_parallel */ into ut_output_buffer_tmp(output_id, message_id, text, item_type)
70-
values (self.output_id, self.last_write_message_id, a_text, a_item_type);
67+
values (self.output_id, ut_output_buffer_tmp_seq.nextval, a_text, a_item_type);
7168
end if;
7269
commit;
7370
end if;
@@ -99,7 +96,6 @@ create or replace type body ut_output_table_buffer is
9996
l_buffer_texts ut_varchar2_rows;
10097
l_buffer_item_types ut_varchar2_rows;
10198
l_finished_flags ut_integer_list;
102-
l_last_read_message_id integer;
10399
l_already_waited_sec number(10,2) := 0;
104100
l_finished boolean := false;
105101
l_sleep_time number(2,1);
@@ -108,33 +104,30 @@ create or replace type body ut_output_table_buffer is
108104
l_producer_finished boolean := false;
109105

110106
procedure get_data_from_buffer_table(
111-
a_last_read_message_id in out nocopy integer,
112107
a_buffer_texts out nocopy ut_varchar2_rows,
113108
a_buffer_item_types out nocopy ut_varchar2_rows,
114109
a_finished_flags out nocopy ut_integer_list
115110
) is
116111
lc_bulk_limit constant integer := 20000;
117112
pragma autonomous_transaction;
118113
begin
119-
a_last_read_message_id := coalesce(a_last_read_message_id,0);
120114
delete /*+ no_parallel */ from (
121115
select /*+ no_parallel */ *
122-
from ut_output_buffer_tmp o
123-
where o.output_id = self.output_id
124-
and o.message_id <= a_last_read_message_id + lc_bulk_limit
125-
order by o.message_id
116+
from ut_output_buffer_tmp a
117+
where a.output_id = self.output_id
118+
and a.message_id <= (select min(message_id) from ut_output_buffer_tmp o where o.output_id = self.output_id) + lc_bulk_limit
119+
order by a.message_id
126120
) d
127121
returning d.text, d.item_type, d.is_finished
128122
bulk collect into a_buffer_texts, a_buffer_item_types, a_finished_flags;
129-
a_last_read_message_id := a_last_read_message_id + a_finished_flags.count;
130123
commit;
131124
end;
132125
begin
133126
while not l_finished loop
134127

135128
l_sleep_time := case when l_already_waited_sec >= 1 then 0.5 else 0.1 end;
136129
l_lock_status := self.get_lock_status();
137-
get_data_from_buffer_table( l_last_read_message_id, l_buffer_texts, l_buffer_item_types, l_finished_flags );
130+
get_data_from_buffer_table( l_buffer_texts, l_buffer_item_types, l_finished_flags );
138131

139132
if l_buffer_texts.count > 0 then
140133
l_already_waited_sec := 0;

‎source/core/ut_utils.pkb

Copy file name to clipboardExpand all lines: source/core/ut_utils.pkb
+41-7Lines changed: 41 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -92,8 +92,8 @@ create or replace package body ut_utils is
9292
a_max_output_len in number := gc_max_output_string_length
9393
) return varchar2 is
9494
l_result varchar2(32767);
95-
c_length constant integer := coalesce( length( a_value ), 0 );
96-
c_max_input_string_length constant integer := a_max_output_len - coalesce( length( a_quote_char ) * 2, 0 );
95+
c_length constant integer := coalesce( lengthb( a_value ), 0 );
96+
c_max_input_string_length constant integer := a_max_output_len - coalesce( lengthb( a_quote_char ) * 2, 0 );
9797
c_overflow_substr_len constant integer := c_max_input_string_length - gc_more_data_string_len;
9898
begin
9999
if c_length = 0 then
@@ -112,8 +112,8 @@ create or replace package body ut_utils is
112112
a_max_output_len in number := gc_max_output_string_length
113113
) return varchar2 is
114114
l_result varchar2(32767);
115-
c_length constant integer := coalesce(dbms_lob.getlength(a_value), 0);
116-
c_max_input_string_length constant integer := a_max_output_len - coalesce( length( a_quote_char ) * 2, 0 );
115+
c_length constant integer := coalesce(ut_utils.lengthb_clob(a_value), 0);
116+
c_max_input_string_length constant integer := a_max_output_len - coalesce( lengthb( a_quote_char ) * 2, 0 );
117117
c_overflow_substr_len constant integer := c_max_input_string_length - gc_more_data_string_len;
118118
begin
119119
if a_value is null then
@@ -135,7 +135,7 @@ create or replace package body ut_utils is
135135
) return varchar2 is
136136
l_result varchar2(32767);
137137
c_length constant integer := coalesce(dbms_lob.getlength(a_value), 0);
138-
c_max_input_string_length constant integer := a_max_output_len - coalesce( length( a_quote_char ) * 2, 0 );
138+
c_max_input_string_length constant integer := a_max_output_len - coalesce( lengthb( a_quote_char ) * 2, 0 );
139139
c_overflow_substr_len constant integer := c_max_input_string_length - gc_more_data_string_len;
140140
begin
141141
if a_value is null then
@@ -412,7 +412,7 @@ create or replace package body ut_utils is
412412
if a_list is null then
413413
a_list := ut_varchar2_rows();
414414
end if;
415-
if length(a_item) > gc_max_storage_varchar2_len then
415+
if lengthb(a_item) > gc_max_storage_varchar2_len then
416416
append_to_list(
417417
a_list,
418418
ut_utils.convert_collection(
@@ -468,7 +468,7 @@ create or replace package body ut_utils is
468468
l_result := ut_varchar2_rows();
469469
for i in 1 .. a_collection.count loop
470470
l_result.extend();
471-
l_result(i) := substr(a_collection(i),1,gc_max_storage_varchar2_len);
471+
l_result(i) := substrb(a_collection(i),1,gc_max_storage_varchar2_len);
472472
end loop;
473473
end if;
474474
return l_result;
@@ -990,5 +990,39 @@ create or replace package body ut_utils is
990990

991991
return l_result;
992992
end;
993+
994+
995+
/*
996+
* Inspired by
997+
* https://stackoverflow.com/a/48782891
998+
*/
999+
function lengthb_clob( a_clob clob) return integer is
1000+
l_blob blob;
1001+
l_desc_offset PLS_INTEGER := 1;
1002+
l_src_offset PLS_INTEGER := 1;
1003+
l_lang PLS_INTEGER := 0;
1004+
l_warning PLS_INTEGER := 0;
1005+
l_result integer;
1006+
begin
1007+
if a_clob = empty_clob() then
1008+
l_result := 0;
1009+
elsif a_clob is not null then
1010+
dbms_lob.createtemporary(l_blob,true);
1011+
dbms_lob.converttoblob
1012+
( l_blob
1013+
, a_clob
1014+
, dbms_lob.getlength(a_clob)
1015+
, l_desc_offset
1016+
, l_src_offset
1017+
, dbms_lob.default_csid
1018+
, l_lang
1019+
, l_warning
1020+
);
1021+
l_result := length(l_blob);
1022+
dbms_lob.freetemporary(l_blob);
1023+
end if;
1024+
return l_result;
1025+
end;
1026+
9931027
end ut_utils;
9941028
/

‎source/core/ut_utils.pks

Copy file name to clipboardExpand all lines: source/core/ut_utils.pks
+5Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -476,6 +476,11 @@ create or replace package ut_utils authid definer is
476476
* Return value of interval in plain english
477477
*/
478478
function interval_to_text(a_interval yminterval_unconstrained) return varchar2;
479+
480+
/*
481+
* Return length of CLOB in bytes. Null for NULL
482+
*/
483+
function lengthb_clob( a_clob clob) return integer;
479484

480485
end ut_utils;
481486
/

‎source/uninstall_objects.sql

Copy file name to clipboardExpand all lines: source/uninstall_objects.sql
+4Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -333,8 +333,12 @@ drop type ut_output_buffer_base force;
333333

334334
drop table ut_output_buffer_tmp purge;
335335

336+
drop table ut_output_buffer_tmp_seq purge;
337+
336338
drop table ut_output_clob_buffer_tmp purge;
337339

340+
drop table ut_output_clob_buffer_tmp_seq purge;
341+
338342
drop table ut_output_buffer_info_tmp purge;
339343

340344
drop package ut_session_context;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.