Maak eerst een tabel waar de BLOB’s in moeten komen:
create table blobs
( timestamp_ bigint not null
, division int not null
, id uniqueidentifier not null
, documentid uniqueidentifier not null
, attachment varbinary(max) not null
)
alter table Blobs add constraint blb_pk primary key(id)
create table AttachmentSettings
( division int not null
, timestampmax bigint not null
)
alter table AttachmentSettings add constraint asg_pk primary key(division)
Het laden gaat met bijvoorbeeld:
declare
l_timestamp_start int64;
l_timestamp_start_incl int64;
l_timestamp_next_min int64;
l_cnt_dct_loaded int64;
l_cnt_dct_ptn_loaded int64;
--
-- Maximum number of documents to load per run across all companies.
--
g_max_dct_load pls_integer := 5000;
--
-- Some maximum step size for timestamp of Exact Online.
--
g_step_size pls_integer := 1000000;
begin
l_cnt_dct_loaded := 0;
--
use [INVANTIVE-EOL]@sqlserver;
use all@eol;
--
for r_ptn in
( select to_number(spn.code) divisioncode
from SYSTEMPARTITIONS@DataDictionary spn
where spn.is_selected = true
and spn.provider_name = 'ExactOnlineAll'
order
by spn.code
)
loop
if l_cnt_dct_loaded < g_max_dct_load
then
begin
--
-- Retrieve timestamp number of the last document.
--
select /*+ ods(false) */
timestampmax
into l_timestamp_start
from AttachmentSettings@sqlserver
where division = r_ptn.divisioncode
;
exception
when no_data_found
then
insert into AttachmentSettings@sqlserver
( division
, timestampmax
)
values
( r_ptn.divisioncode
, 0
)
;
l_timestamp_start := 0;
end;
--
use select r_ptn.divisioncode, 'eol';
--
-- Find first document along increasing value of timestamp.
--
dbms_output.put_line('Select document attachment for division ' || to_char(r_ptn.divisioncode) || ' from beyond timestamp ' || to_char(l_timestamp_start) || '.');
select min(timestamp)
into l_timestamp_start_incl
from ( select timestamp
from syncdocumentattachments@eol
where division = r_ptn.divisioncode
and timestamp > l_timestamp_start
limit 1
)
;
--
-- Load when there are any documents.
--
if l_timestamp_start_incl is not null
then
l_timestamp_next_min := l_timestamp_start_incl + g_step_size;
l_cnt_dct_ptn_loaded := 0;
--
dbms_output.put_line('Select document attachment for division ' || to_char(r_ptn.divisioncode) || ' to at most ' || to_char(l_timestamp_next_min) || '.');
for r_dct in
( select /*+ ods(false) http_disk_cache(false) http_memory_cache(false) */
sdt.document documentid
, sdt.url
, sdt.timestamp
, atl.DocumentContents
, sdt.ID attachmentid
from SyncDocumentAttachments@eol sdt
join AttachmentByUrl@eol(sdt.url) atl
where sdt.division = r_ptn.divisioncode
and sdt.timestamp >= l_timestamp_start_incl
and sdt.timestamp <= l_timestamp_next_min
)
loop
if r_dct.timestamp > l_timestamp_start_incl
then
l_timestamp_start_incl := r_dct.timestamp;
end if;
--
-- Save to table.
--
begin
insert into Blobs@sqlserver
( timestamp_
, division
, id
, documentid
, attachment
)
values
( r_dct.timestamp
, r_ptn.divisioncode
, r_dct.attachmentID
, r_dct.Documentid
, r_dct.DocumentContents
);
exception
when others
then
dbms_output.put_line('Error loading #' || to_char(l_cnt_dct_ptn_loaded+1) || ' with ID ' || r_dct.attachmentid || ' for document ID ' || r_dct.documentid || ': ' || sqlerrm);
null;
end;
--
l_cnt_dct_loaded := l_cnt_dct_loaded + 1;
l_cnt_dct_ptn_loaded := l_cnt_dct_ptn_loaded + 1;
dbms_output.put_line('Register attachment #' || to_char(l_cnt_dct_ptn_loaded) || ' with ID ' || r_dct.attachmentid || ' for document ID ' || r_dct.documentid || '.');
--
if l_cnt_dct_loaded > g_max_dct_load
then
dbms_output.put_line('Loaded ' || to_char(l_cnt_dct_loaded) || ' documents exceeds limit of ' || to_char(g_max_dct_load) || '.');
exit;
end if;
end loop;
--
-- Move forward to end of range when no documents were found.
--
if l_cnt_dct_ptn_loaded = 0 and l_timestamp_start_incl < l_timestamp_next_min
then
l_timestamp_start_incl := l_timestamp_next_min;
end if;
--
update AttachmentSettings@sqlserver
set timestampmax = l_timestamp_start_incl
where division = r_ptn.divisioncode
;
dbms_output.put_line('Finished company ' || r_ptn.divisioncode || ' at timestamp ' || l_timestamp_start_incl || ' (including).');
else
dbms_output.put_line('Skip company ' || r_ptn.divisioncode || ', no new documents beyond timestamp ' || to_char(l_timestamp_start) || '.');
end if;
else
dbms_output.put_line('Skip company ' || r_ptn.divisioncode || ', maximum document load exceeded.');
end if;
end loop;
end;