Het volgende script laadt massaal relaties in Exact Online, in dit geval vanuit een tekst met meerdere relaties vanuit een tekst in CSV-formaat. Het gebruikt synchronize (zie Exact Online synchroniseren met Visma.net Financialss):
set use-http-disk-cache@eol false
set use-http-memory-cache@eol false
create or replace table accounts_soll@inmemorystorage
as
select *
from csvtable
( passing '123|Legato Inc#124|Philips#125|Oracle#126|Data Kings'
row delimiter '#'
column delimiter '|'
columns code varchar2 position next
, name varchar2 position next
)
create or replace table ExactOnlineAccountsIst@inMemoryStorage
as
select /*+ http_disk_cache(false) http_memory_cache(false) */
act.Id
, act.Code
, act.Name
, act.VATNumber VATNumberOriginal
, replace(act.VATNumber, ' ', '') VATNumberNormalized
from AccountsIncremental@eol act
--
-- Synchroniseer de relaties van CSV naar Exact Online.
--
synchronize accounts_soll@inmemorystorage
to ExactOnlineAccountsIst@inMemoryStorage
with insert
exit after 1000 rows /* Avoid too many loads when things go wrong. */
or
update all
except
AddressLine1
, AddressLine2
, City
, Country
, Postcode
, VATNumberOriginal
, CreatedMin
, rowid$
identified by code
batchsize 1
continue on first 100 errors
triggers
instead of insert
declare
l_account_name varchar2;
l_account_created datetime;
l_label varchar2;
begin
l_label := 'code ' || :new.code || ' - ' || :new.name;
dbms_output.put_line('instead of insert: ' || l_label);
insert into ExactOnlineREST..Accounts@eol
( Code
, Name
)
values
( :new.Code
, :new.Name
);
end
--
instead of update
declare
l_label varchar2;
begin
l_label := 'Code ' || :new.code || ' - ' || :new.name;
dbms_output.put_line('instead of update Accounts ' || l_label || '.');
update ExactOnlineREST..Accounts@eol
set Name = :new.Name
where Id = :old.Id
;
exception
when others
then
dbms_output.put_line('Error on update Accounts on ' || l_label || ' due to ' || sqlerrm);
raise;
end