Après quelques tests, nous avons
- un script qui fonctionne toujours
- un autre qui produit cette erreur tout le temps, dès la toute première instruction interrogeant Exact Online.
Voici celui qui produit l’erreur :
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
– envoyer un e-mail
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;
– laissez une ligne vide ci-dessus, sinon la procédure stockée entre en conflit avec le bloc SQL ci-dessous.
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)*86400,0); – en secondes.
l_debug := 'le script se termine. Durée totale du script: ' || l_scriptduration || 's.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
sendmail(
– sujet de l'email ici
'script de synchronisation pour les tables EOL sauf transactionlines : succès.'
– corps de l'email ici
, "statut du script : OK."
|| 'heure de début du script : ' || to_char(l_scriptstart, 'YYYY-MM-DD HH24:MI:SS') || ''
|| 'durée du script : ' || l_scriptduration || ' secondes.' || ''
|| 'journal complet ci-dessous' || ''
|| replace(l_log, chr(13), '')
– destinataires ici
,l_emailrecipients
);
exception
when others
then
l_debug := 'Erreur attrapée dans l'exception : ' || 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)*86400,0); – en secondes. recalculé ici car l'exception l'a sauté.
sendmail(
– sujet de l'email ici
'script de synchronisation pour les tables EOL sauf transactionlines : ERREUR. Une exception SQL est survenue.'
– corps de l'email ici
, "statut du script : ERREUR. Une exception SQL est survenue."
|| 'heure de début du script : ' || to_char(l_scriptstart, 'YYYY-MM-DD HH24:MI:SS') || ''
|| 'durée du script : ' || l_scriptduration || ' secondes.' || ''
|| 'journal complet ci-dessous' || ''
|| replace(l_log, chr(13), '')
– destinataires ici
,l_emailrecipients
);
end;