Seems there is an issue with Beta 24.1.113 and Fortnox when calling a stored table @inmemorystorage inside a loop:
--
-- Case with DataDictionary : works!
--
create or replace table test@inmemorystorage
as
select *
from Calendar@DataDictionary
limit 10
select * from test@inmemorystorage
declare
my_query text;
begin
for r in
( select *
from test@inmemorystorage
)
loop
my_query := my_query || 'my text' || r.Day_In_Week;
end loop;
--
dbms_output.put_line(my_query);
end;
--
-- Case with Fortnox : raises itgensql151 error.
--
create or replace table accounts@inmemorystorage
as
select *
from Accounts@fortnox
where Active = true
;
select *
from accounts@inmemorystorage
declare
my_query text;
begin
for r in
( select *
from accounts@inmemorystorage
)
loop
my_query:= my_query || 'my text' || r.Description ;
end loop;
--
dbms_output.put_line(my_query);
end;
Error:
itgensql151:
Parse error on line 6 at column 42:
Unknown field or parameter ‘R.DESCRIPTION’.
Possible valid alternative symbol names: R.Description.
This issue is not raised in Invantive Cloud, see Faster way to retrieve account balances on Fortnox
Strange workaround has been found.
The fields need to be named as to be found in the loop statement:
declare
my_query text;
begin
for r in (select Description as Description from accounts@inmemorystorage)
loop
my_query:= my_query || 'my text' || r.Description ;
end loop;
dbms_output.put_line(my_query);
end;
To be added: it seems the datatype resulting from the Fortnox connector are not correct, they seems like array
itgenrst004
The type of the actual data must match the data type of the column ‘ACCOUNT’ in ‘VOUCHERLINES’.
Ensure that the actual data type (çurrently ‘String’) in row #1 is cast to the data type (current ‘int32’).
itgensql151
The itgensql151 error can be reproduced on 24.1.114-BETA using:
declare
l_x varchar2;
begin
for r in (select dummy_string from DUAL@DataDictionary)
loop
l_x := r.dummy_string;
end loop;
end;
It is probably caused by a major recent refactoring of column naming technology. A developer will work on a bug fix.
itgenrst004
The itgenrst004 error has already been solved in 23.1.115-BETA.
There is currently no solution or ETA.
A fix for the naming issue described in this topic will be shipped with 24.1.122-BETA. This release is scheduled for availability early in the week of July 3, 2025.
Included in this build are also a number of other improvements due to the new naming and recognition algorithm for identifiers in SQL and PSQL.
This question was automatically closed after at least 1 week of inactivity after a possible solution was provided. The last answer given has been marked as a solution.
Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.
