Efter en smule test har vi
- et script, der altid fungerer
- et, der altid frembringer denne fejl ved den allerførste instruktion, der forespørger Exact Online.
Dette er det, der frembringer fejlen:
use ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol, ****@eol;
Create or Replace procedure sendmail
( email_subject text
, email_body text
, email_recipients text
)
as
declare
l_error_txt varchar2;
l_error_cnt varchar2;
l_email_table varchar2;
begin
set smtp-host-address@mail ‘smtp.office365.com’;
set smtp-user-name@mail ‘user@mycompany.com’;
set smtp-password@mail ‘****’;
set [mail-from-name]@mail ‘systems’;
set [mail-from-email]@mail ‘reporting@mycompany.com’;
set smtp-enable-ssl@mail true;
set smtp-minimum-deliver-duration-ms@mail 1000;
set mail-body-html@mail true;
set mail-priority@mail 0;
begin
– sending email
insert into smtp@mail
( toEmail
, ccEmail
, bccEmail
, subject
, body
–, attachmentContents – starting PROD 20.2.112 use Attachment1Contents instead
–, attachmentName
–, attachmentMimeType
)
select email_recipients
toEmail
, cast(null as varchar2)
ccEmail
, cast(null as varchar2)
bccEmail
, email_subject
subject
, email_body
body
–, null – rfe.file_contents
– attachmentContents – starting PROD 20.2.112 use Attachment1Contents instead
–, null – basename(‘{USER_MULTI_PARTIES_FILE_NAME}’)
– attachmentName
–, null – cast(null as varchar2)
– attachmentMimeType
;
end;
end;
– leave a blank line above, otherweise the stored procedure conflicts with the sql block below.
declare
l_scriptstart datetime;
l_stmtstart datetime;
l_sqlresult INT;
l_log varchar2;
l_debug varchar2;
l_scriptsuccess boolean;
l_scriptduration varchar2;
l_emailrecipients text := ‘user1@mycompany.com,user2@mycompany.com’;
–l_emailrecipients text := ‘user1@mycompany.com’;
begin
l_scriptstart := sysdate;
l_debug := 'script started at ’ || l_scriptstart;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_rest_cashflowpaymentsbulk@sqlserver as select * from paymentsbulk@eol where EntryDate > dateadd(‘year’,-2,sysdate) and isbatchbooking = 1;
l_sqlresult := sqlrowcount;
l_debug := 'paymentsbulk@eol with filter on EntryDate>(today-2 years) and isbatchbooking = 1 updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_rest_glaccountsbulk@sqlserver as select * from ExactOnlineREST.Financial.glaccountsbulk@eol;
l_sqlresult := sqlrowcount;
l_debug := 'ExactOnlineREST.Financial.glaccountsbulk@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_rest_GlClassificationsBulk@sqlserver as select * from ExactOnlineREST.Financial.GLClassificationsBulk@eol;
l_sqlresult := sqlrowcount;
l_debug := 'ExactOnlineREST.Financial.GLClassificationsBulk@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_rest_GLAccountClassificationMappings@sqlserver as select * from ExactOnlineREST.GLAccountClassificationMappings@eol;
l_sqlresult := sqlrowcount;
l_debug := 'ExactOnlineREST.GLAccountClassificationMappings@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_xml_balancelines@sqlserver as select * from ExactOnlineXML.XML.BalanceLines@eol;
l_sqlresult := sqlrowcount;
l_debug := 'ExactOnlineXML.XML.BalanceLines@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
–create or replace table eol_rest_accountsbulk@sqlserver as select * from accountsbulk@eol;
create or replace table eol_rest_accountsbulk@sqlserver as select * from syncAccounts@eol where Timestamp >0 batchsize 50000; – adjust the batchsize (default: 3000)
– the sync statement run in approx 2 mins when cache is up to date. the Bulk runs in approx 5,2 minutes. 135710 records as of 26/06/2024.
– sync endpoint has a bit less info (156 vs 190 fields)
l_sqlresult := sqlrowcount;
l_debug := 'sync.account@eol updated via SYNC. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_rest_agingreceivableslist@sqlserver as select * from AgingReceivablesList@eol;
l_sqlresult := sqlrowcount;
l_debug := 'AgingReceivablesList@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_rest_agingpayableslist@sqlserver as select * from AgingPayablesList@eol;
l_sqlresult := sqlrowcount;
l_debug := 'AgingPayablesList@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table eol_rest_ReportingBalance@sqlserver as select * from ReportingBalance@eol where reportingYear >= to_char(dateadd(‘year’,-2,sysdate),‘YYYY’) batchsize 50000; – adjust the batchsize (default: 3000)
l_sqlresult := sqlrowcount;
l_debug := 'ReportingBalance@eol with filter reportingYear on the last 3 years updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table EOL_REST_CURRENTYEAR_OPENIGNBALANCE@sqlserver as select * from CurrentYearAfterEntry@eol;
l_sqlresult := sqlrowcount;
l_debug := 'CurrentYearAfterEntry@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_stmtstart := sysdate;
create or replace table EOL_REST_PREVIOUSYEAR_OPENIGNBALANCE@sqlserver as select * from PreviousYearAfterEntry@eol;
l_sqlresult := sqlrowcount;
l_debug := 'PreviousYearAfterEntry@eol updated. duration: ’ || round((sysdate-l_stmtstart)6060*24,0) || 's. number of records: ’ || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ’ ’ || l_debug || chr(13);
l_scriptduration := round((sysdate-l_scriptstart)*606024,0); – i sekunder.
l_debug := 'script slutter. Samlet scriptvarighed: ' || l_scriptduration || 's.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
sendmail(
– emne for e-mail her
'synkroniseringsscript for eol-tabeller undtagen transactionlines: succes.'
– e-mailtekst her
,'script status: OK.' || ''
|| 'script starttidspunkt: ' || to_char(l_scriptstart, 'YYYY-MM-DD HH24:MI:SS') || ''
|| 'scriptvarighed: ' || l_scriptduration || ' sekunder.' || ''
|| 'fuld log nedenfor' || ''
|| replace(l_log, chr(13), '')
– modtagere her
,l_emailrecipients
);
exception
when others
then
l_debug := 'Fejl registreret i undtagelse: ' || chr(13) || sqlerrm;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_scriptsuccess := false;
l_scriptduration := round((sysdate-l_scriptstart)*606024,0); – i sekunder. Beregnet igen her, fordi undtagelse sprang det over.
sendmail(
– emne for e-mail her
'synkroniseringsscript for eol-tabeller undtagen transactionlines: FEJL. En SQL-undtagelse opstod.'
– e-mailtekst her
,'script status: FEJL. En SQL-undtagelse opstod.' || ''
|| 'script starttidspunkt: ' || to_char(l_scriptstart, 'YYYY-MM-DD HH24:MI:SS') || ''
|| 'scriptvarighed: ' || l_scriptduration || ' sekunder.' || ''
|| 'fuld log nedenfor' || ''
|| replace(l_log, chr(13), '')
– modtagere her
,l_emailrecipients
);
end;