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         => 'http://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:

1) 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

2) Run your report again.

3) Login on https://www.apexofficeprint.com and go to Remote Debug

4) 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.

5) 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, click the button "Sent to Support" and contact support@apexofficeprint.com.

-) 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, click the button "Sent to Support" and send an email to support@apexofficeprint.com 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:

  1. Go in your application to Shared Components > Component Settings > APEX Office Print (AOP) [Plug-in] and set Debug to Local.

  2. 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.

  3. 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, contact support@apexofficeprint.com.

    -) 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, send an email to support@apexofficeprint.com as it means there's a bug in the conversion.

  4. If you didn't find a solution yourself, please send the JSON file to support@apexofficeprint.com

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;
/