Debugging
Debugging in APEX
AOP is fully instrumented with APEX debug messages, so when you turn debugging on in your APEX application you will see many AOP: ... calls. Depending the level of APEX debug mode, more detailed debug output will be available. See Dimitri Gielis's blog post how to put APEX Trace on, which will show the most debug messages.
You can also specify a global variable g_debug_procedure if you want to call your own debug procedure which for examples logs the information in your own table.
If you use Logger, you can also enable logger by compiling aop_api_pkg with a PL/SQL flag.
ALTER PACKAGE aop_api_pkg COMPILE PLSQL_CCFLAGS = 'logger_on:TRUE';
Debugging in PL/SQL
If you're scheduling reports or calling the AOP packages with PL/SQL you can debug straight from PL/SQL. Here's an example:
declare
l_binds wwv_flow_plugin_util.t_bind_list;
l_return blob;
l_output_filename varchar2(100) := 'output';
begin
-- remove previous debug
apex_debug.remove_debug_by_age(
p_application_id => 232,
p_older_than_days => -1);
-- create an APEX session with the debug enabled
aop_api_pkg.create_apex_session(
p_app_id => 232,
p_enable_debug => 'Y');
-- in case you want to write the debug output to your own table, set the g_debug_procedure
aop_api_pkg.g_debug_procedure := 'aop_sample_pkg.custom_debug';
l_return := aop_api_pkg.plsql_call_to_aop (
p_data_type => aop_api_pkg.c_source_type_rpt,
p_data_source => 'ir1',
p_template_type => aop_api_pkg.c_source_type_apex,
p_template_source => 'aop_interactive.docx',
p_output_type => 'pdf',
p_output_filename => l_output_filename,
p_binds => l_binds,
p_aop_url => 'https://api.apexofficeprint.com/',
p_api_key => 'your API key',
p_app_id => 232,
p_page_id => 5,
p_init_code => 'aop_api_pkg.g_language := ''en'';');
end;
/
Remote Debugging
If you receive an error and you need some help of us, follow next steps:
- Go in your application to Shared Components > Component Settings > APEX Office Print (AOP) [Plug-in] and enable remote debugging (see parameters screenshot)
Note 1: you find your API key when you login in your dashboard on https://www.apexofficeprint.com Note 2: Make sure APEX_050000 or APEX_050100 schema has the rights to connect to http(s)://www.apexofficeprint.com
Run your report again.
Login on https://www.apexofficeprint.com and go to Remote Debug
Click on the magnifying glass, and the JSON that was generated behind the scenes which is send to the AOP server component will be shown.
Investigate the received JSON:
-) JSON is valid, and valid output: this is how it should be.
-) JSON is invalid: this is probably due to your version of APEX. If you are below APEX 5.1.2, you need a patch linked to apex_json.
-) JSON is valid, but invalid Office file (Word, Excel, PowerPoint): this means that AOP couldn't merge your data with the template you provided. If the template and requested output is the same format (e.g. your template is in Word and you request a Word document); check your template again if all substitution strings are correct. If you believe everything is ok, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us.
-) JSON is valid, but invalid PDF (or other output format): if the template and output format are different, a conversion is going on handled by LibreOffice or MS Office. Either the conversion goes wrong, or most likely the initial file before the conversion was already wrong. To debug further, set the output format to the same format as your template and run your report again. If the output is invalid, follow previous steps (see JSON is valid, but invalid Office file). If the output is ok, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us, as it means there's a bug in the conversion.
Local Debugging
If you receive an error and you need some help of us, do following steps:
Go in your application to Shared Components > Component Settings > APEX Office Print (AOP) [Plug-in] and set Debug to Local.
Run your report again. A JSON file will now be downloaded instead of the report. This JSON is what is sent to the AOP server component behind the scenes.
Investigate the received JSON:
-) JSON is valid, and valid output: this is how it should be.
-) JSON is invalid: this is probably due to your version of APEX. If you are below APEX 5.1.2, you need a patch linked to apex_json.
-) JSON is valid, but invalid Office file (Word, Excel, PowerPoint): this means that AOP couldn't merge your data with the template you provided. If the template and requested output is the same format (e.g. your template is in Word and you request a Word document); check your template again if all substitution strings are correct. If you believe everything is ok, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us.
-) JSON is valid, but invalid PDF (or other output format): if the template and output format are different, a conversion is going on handled by LibreOffice or MS Office. Either the conversion goes wrong, or most likely the initial file before the conversion was already wrong. To debug further, set the output format to the same format as your template and run your report again. If the output is invalid, follow previous steps (see JSON is valid, but invalid Office file). If the output is ok, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us.
If you didn't find a solution yourself, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us.
Debugging connections to AOP Server
Open a first command prompt or shell and run AOP in verbose mode:
./APEXOfficePrintLinux64 —-verbose
Copy the test.json file to your server where AOP is running in /tmp folder
In another command prompt or shell go to /tmp folder and run (If you are in windows environment you can download curl from https://curl.haxx.se/download.html#Win64):
curl -X POST -H 'Content-Type: application/json' -d @test.json http://127.0.0.1:8010/ > output.docx
This should show the below output and have created an output.docx file
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 164k 100 68310 100 97k 335k 492k --:--:-- --:--:-- --:--:-- 494k
Check the first shell, AOP should have printed:
Prinjob received.
Sending back response.
Prinjob completed.
Try to run the same curl command but with your server name
curl -X POST -H 'Content-Type: application/json' -d @test.json http://<your server name>:8010/ > output.docx
Same as before for output.
Go to the database server copy the test.json in the /tmp folder and run the curl command again
curl -X POST -H 'Content-Type: application/json' -d @test.json http://<your server name>:8010/ > output.docx
This should do the same as before.
Instead of using curl you can also use a RESTClient like Postman. You will have to add the header 'Content-Type: application/json' and give the data from the test.json into the body section.
Go into APEX > SQL Workshop, and try to connect to the AOP server
select apex_web_service.make_rest_request('http://<your server name>:8010/', 'GET') from dual
Try to generate a first document, adjust the below script to your settings and run
declare
l_return blob;
l_output_filename varchar2(100) := 'output';
begin
aop_api_pkg.g_proxy_override := null; -- set the proxy if you use that
l_return := aop_api_pkg.plsql_call_to_aop (
p_data_type => 'SQL',
p_data_source => q'[
select
'file1' as "filename",
cursor(
select 'hello world' as "string"
from dual
) as "data"
from dual
]',
p_template_type => null,
p_template_source => null,
p_output_type => 'docx',
p_output_filename => l_output_filename,
p_aop_url => 'http://<your server name>:8010/', -- change to the AOP server
p_api_key => '',
p_app_id => 232); -- change to the AOP sample app number
sys.htp.p(dbms_lob.getlength(l_return));
end;
In the shell where AOP is running you should see the incoming connection.
Hopefully the below steps give you more insight where your connection is failing so you can correct accordingly.
Checking your query on valid JSON
AOP is using apex_json behind the scenes to generate the necessary JSON the AOP Server understands. Sometimes you get invalid JSON, but it's hard to figure out where in your query it's going wrong. With following PL/SQL code you can test without involving AOP.
declare
c sys_refcursor;
v_clob clob;
v_output varchar2(32000);
begin
open c for
/* THIS SHOULD BE YOUR QUERY */
select to_char(sysdate,'DD-MON-YYYY') as "dd"
from dual;
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write(c);
apex_json.close_object;
v_clob := apex_json.get_clob_output;
-- output
while length(v_clob) > 0 loop
begin
if length(v_clob) > 16000 then
v_output:= substr(v_clob,1,16000);
sys.htp.prn(v_output);
v_clob:= substr(v_clob,length(v_output)+1);
else
v_output := v_clob;
sys.htp.prn(v_output);
v_clob:='';
v_output := '';
end if;
end;
end loop;
apex_json.free_output;
end;
/