We have the following statement that get the latest timestamp from a TransactionLines-table (synced from Exact Online by Invantive) from SQL Server for each division:
create or replace table timestamp_table@inmemorystorage
( last_timestamp Datetime
, division int
);
create or replace table to_insertupdate@inmemorystorage
as
select *
from ExactOnlineREST.Sync.SyncTransactionLines@eol LIMIT 0
;
--
-- Get the max_timestamp per division from SQL Server.
--
Declare
Max_timeStamp_dest Bigint;
Records_to_insert_update int;
Records_to_delete int;
Records_inserted_updated int;
Begin
for r in
(
execute native 'select MAX(TimeStamp) as last_timestamp, Division as division from eol_rest_transactionlinesincremental group by division'
datacontainer 'sqlServer'
)
loop
insert into timestamp_table@inmemorystorage (last_timestamp, division) values (r.last_timestamp, r.division);
end loop;
end;
To verify, the statement
select *
from timestamp_table@inmemorystorage
gives the following result:
Then we run second statement that should insert into to_insertupdate@inmemorystorage
the records that have changed in Exact Online, based on the last timestamp value pulled from SQL Server:
Declare
l_sql varchar2;
begin
for r in
( select *
from timestamp_table@inmemorystorage
)
loop
create or replace table temp@inmemorystorage
as
select *
from ExactOnlineREST.Sync.SyncTransactionLines@eol E
where E.timestamp > r.last_timestamp
and E.division = r.division
;
l_sql := sqlrowcount;
dbms_output.put_line('found ' || l_sql || ' records for division ' || r.division || chr(10));
synchronize temp@inmemorystorage to to_insertupdate@inmemorystorage with insert identified by id;
l_sql := sqlerrm;
dbms_output.put_line(l_sql || chr(10));
end loop;
end;
We loop on the timestamp_tabletemp@inmemorystorage and call @eol to get the values modified/created since then, insert them in a temp@inmemorystorage by a create or replace, then synchronize with insert in the table to_insertupdate@inmemorystorage
Unfortunately, the synchronize does no write anything.
If the following statement is executed out of the loop, it saves values:
`synchronize temp@inmemorystorage to to_insertupdate@inmemorystorage with insert identified by id
We would like to use the synchronize with insert because it prevent to enumerate the fields and fields values in a insert stmt.