Itgensql144: The JSON has an invalid format

Questions
,
2

Please note that the PSD2 bank statements are in UTF16 encoding instead of UTF-8. That might explain the error. Can you try logic such as:

select ...
from   transactionlinesbulk@${EOL_ROTATOR} tle
on     tle.accountcode   = ive.eol_invoice_to_code
and    tle.amountdc      != 0
and    tle.journalcode   = '20'
and    tle.invoicenumber is not null
and    tle.glaccount 
       in 
       ( select /*+ low_cost */ id 
         from   exactonlinerest..glaccountsincremental@${EOL_ROTATOR} 
         where  code ='1100' 
       )
join   ExactOnlineREST..DocumentAttachmentFiles@${EOL_ROTATOR} dae
on     dae.division = tle.division
and    dae.document = tle.document
and    dae.filename like 'Statement %.txt'
join   AttachmentByUrl@${EOL_ROTATOR}(dae.url || '&Download=1') dct
join   jsontable
       ( 'Transactions[*]'
         passing blob_to_text(dct.DocumentContents, 'Unicode')
         columns TransactionDateTimeUtc            datetime path 'TransactionDateTimeUtc'
         ,       Description                       varchar2 path 'Description'
         ,       CounterPartyName                  varchar2 path 'CounterParty.Name'
         ,       CounterPartyAccountIdentification varchar2 path 'CounterParty.BankEntity.AccountIdentification'
       ) jte

The use of blob_to_text(dct.DocumentContents, 'Unicode') ensures the conversion to text.

Please upgrade to a more recent 22.0 or BETA 22.1 version when the blob_to_text does not work.