Sinds ongeveer een maand werkt onderstaand script niet meer als Applicatie Module in Invantive Cloud. Het doel is om een Excel-bestand (te selecteren door gebruiker) in te lezen met data naar Exact Online.
Gebaseerd op voorbeeld uit:
declare
p_run boolean;
--
--
l_errors_1 varchar2;
l_filename varchar2;
l_payload blob;
l_query varchar2;
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_cnt number;
l_cnt_missing_item number;
l_cnt_missing_account number;
l_list_missing_items varchar2;
l_list_missing_accounts varchar2;
begin
--
-- Get parameters.
--
p_run := cast(cloud_http. get_request_form_value('p_run') as boolean);
--
if coalesce(p_run, false) = false
then
--
-- Enter parameters.
--
cloud_http.set_use_template(true);
cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));
cloud_http.append_line_to_response_body_text('<form method="post" enctype="multipart/form-data">');
cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');
cloud_http.append_line_to_response_body_text('<ul>');
cloud_http.append_line_to_response_body_text
( '<li><label for="p_file">'
|| translate_resources('{res:itgen_file}')
|| '</label></li><li><input type="file" id="p_file" name="p_file" accept=".xlsx" required/></li>'
);
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Inlezen in Exact Online"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Process form.
--
l_filename := cloud_http.get_request_form_file_name('p_file');
--
if l_filename is null
then
raise_application_error('sample001', 'The file name is empty.');
end if;
--
l_payload := cloud_http.get_request_form_file_contents('p_file');
--
if l_payload is null
then
raise_application_error('sample002', 'The file payload is empty.');
end if;
--
--
-- Transform standard excel spreadsheet into
-- a temporary table.
--
create or replace table test@InMemoryStorage
as
select xlsx.*
from exceltable
( worksheet 'Blad1'
passing l_payload
skip empty rows
skip first 1 rows
columns itemcode varchar2 not null position next
, costprice double not null position next
) xlsx;
--
-- Gebruik test admninistratie 999
--
use 3536261
--
-- Update items.
--
for r
in
( select *
from test@InMemoryStorage
)
loop
update ExactOnlineREST.Logistics.Items@eol
set CostPriceNew = r.costprice
where code = r.itemcode
and division = '3536261'
;
if sqlrowcount != 1
then
l_errors_1 := l_errors_1 || chr(13) || 'Could not find itemcode ' || r.itemcode || ' to update costprice to ' || r.costprice || '.';
end if;
end loop;
end;
Fout
Er is een fout opgetreden tijdens het uitvoeren van een applicatiemodule.
Foutcode: itgensql056
Verzoek-ID: 0HNH4VEJU55BQ:00000017
IP-adres: ::ffff
Bericht-ID: ec6e4b7e-9423-4fed-9c71-813efa36f321
Toepassingsmodule UID: xxx
Toepassingsmodule: TEST.inlezendata3
Database UID: xxx
Database: Exact Online (nl) als xxx
Partij-ID: 441
Syntax error between the two '***' on line 97, column 4:
declare<CRLF> p_run ...f;<CRLF> end loop;<CRLF>end;***<EOF>***
Error: no viable alternative at input 'declare\r\n p_run boolean;\r\n --\r\n --\r\n l_errors_1 varchar2; \r\n l_filename varchar2;\r\n l_payload blob;\r\n l_query varchar2;\r\n l_workbook excel_workbook;\r\n l_worksheet excel_worksheet;\r\n l_binary blob;\r\n l_cnt number; \r\n l_cnt_missing_item number;\r\n l_cnt_missing_account number;\r\n l_list_missing_items varchar2;\r\n l_list_missing_accounts varchar2;\r\nbegin\r\n --\r\n -- Get parameters.\r\n --\r\n p_run := cast(cloud_http. get_request_form_value('p_run') as boolean);\r\n --\r\n if coalesce(p_run, false) = false\r\n then\r\n --\r\n -- Enter parameters.\r\n --\r\n cloud_http.set_use_template(true);\r\n cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));\r\n cloud_http.append_line_to_response_body_text('<form method="post" enctype="multipart/form-data">');\r\n cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');\r\n cloud_http.append_line_to_response_body_text('<ul>');\r\n cloud_http.append_line_to_response_body_text\r\n ( '<li><label for="p_file">' \r\n || translate_resources('{res:itgen_file}') \r\n || '</label></li><li><input type="file" id="p_file" name="p_file" accept=".xlsx" required/></li>'\r\n );\r\n cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Inlezen in Exact Online"/></li>');\r\n cloud_http.append_line_to_response_body_text('</ul>');\r\n cloud_http.append_line_to_response_body_text('</form>');\r\n else\r\n --\r\n -- Process form.\r\n --\r\n l_filename := cloud_http.get_request_form_file_name('p_file');\r\n --\r\n if l_filename is null\r\n then\r\n raise_application_error('sample001', 'The file name is empty.');\r\n end if;\r\n --\r\n l_payload := cloud_http.get_request_form_file_contents('p_file');\r\n --\r\n if l_payload is null\r\n then\r\n raise_application_error('sample002', 'The file payload is empty.');\r\n end if;\r\n --\r\n --\r\n -- Transform standard excel spreadsheet into\r\n -- a temporary table.\r\n --\r\n create or replace table test@InMemoryStorage\r\n as\r\n select xlsx.*\r\n from exceltable\r\n ( worksheet 'Blad1'\r\n passing l_payload\r\n skip empty rows\r\n skip first 1 rows\r\n columns itemcode varchar2 not null position next\r\n , costprice double not null position next\r\n ) xlsx;\r\n --\r\n -- Gebruik test admninistratie 999\r\n --\r\n --use 3536261\r\n --\r\n -- Update items.\r\n --\r\n for r\r\n in \r\n ( select *\r\n from test@InMemoryStorage\r\n )\r\n loop\r\n update ExactOnlineREST.Logistics.Items@eol\r\n set CostPriceNew = r.costprice\r\n where code = r.itemcode\r\n and division = '3536261'\r\n ;\r\n if sqlrowcount != 1\r\n then\r\n l_errors_1 := l_errors_1 || chr(13) || 'Could not find itemcode ' || r.itemcode || ' to update costprice to ' || r.costprice || '.';\r\n end if;\r\n end loop;\r\nend;'.
Graag jullie support