Skip to main content

Troubleshooting

Uncaught ReferenceError: AOP is not defined​

In the console of the browser you get this JavaScript error when the static files of the AOP Plug-in can't be loaded. This is most of the times an issue with the configuration of ORDS (Oracle REST Data Services). To correct this issue, validate ORDS again by using the following command:

java -jar ords.war validate

Output generates invalid Office documents​

In case Office documents are not opening as expected, probably there was an issue with the JSON that was sent to the AOP server. If you run in the AOP Cloud, you can enable Remote Debugging, which you can access when you login in your APEX Office Print dashboard at https://www.apexofficeprint.com. If you're running the on-premise version of AOP, on the server there is a server_error.log file that contains the errors it found during rendering. First step is to check that file for more information. Next make sure that your JSON is valid by validating the JSON in for example http://jsonlint.com or by doing Remote Debug (see further on in this document).

Invalid JSON generated by apex_web_service.make_rest_request​

Make sure APEX 5.0.4 or higher is installed. To download it, login to support.oracle.com.

Invalid JSON generated by apex_json​

Make sure you install an additional patch for APEX 5.0.4 PSE 24341756 or for APEX 5.1.0 PSE 25650850 or for APEX 5.1.1 PSE 25853436 or for APEX 5.1.4 PSE 27963501 available at https://support.oracle.com. Those will fix issues with apex_json which is used behind the scenes in AOP.

You also might hit Oracle database bug (in 12.1.0.2). A patch is made available on https://support.oracle.com; search for patch #21424376. You won't experience this issue in 11.2.0.4 or 12.2.

When using PL/SQL Function returning JSON, you might hit a bug in apex_plugin_util, which can be fixed by applying patch 26048323.

Some people also have special characters in their data which makes the JSON invalid. Here're some regular expressions which help to clean-up the data.

Replace double spaces and non printable characters like tabs and line feeds, by one space

column_name = regexp_replace(column_name , '[[:space:]]{2,}|[^[:print:]]', ' ')

Remove the spaces at the beginning and the end of a field value

column_name = regexp_replace(column_name, '^[[:space:]]{1,}|[[:space:]]{1,}$', '')

Finally if you still encounter issues, use ORDS (SQL Workshop > RESTful Services) as your datasource. ORDS will generate the JSON instead of apex_json and AOP will use that JSON instead (see Data Source - URL with ORDS RESTful Web Service).

Running on Oracle Cloud​

If you are running on Oracle Cloud you must use HTTPS in the AOP Plug-in Component setting and when you call the aop_api_pkg package.

Invalid PDF file​

If you're running the on-premise version of AOP, check a file on the server called server_error.log or server_uncaugt_exceptions.log and see which error you get. Try to run your example in the same format as your template. If this also fails, there's an issue with the JSON or the template. Check your template and data again. If you receive your document fine, but PDF is not working, there's an issue finding LibreOffice or MS Office. Make sure those programs are installed correctly. Try to do a manual conversion on the command line in those tools and see if that works. If not, check the error and try to reinstall. You can also do Remote Debugging (see further on in this document), which will help identifying the real issue. If everything fails, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us.

How to read and convert documents (docx, xlsx, pptx, pdf) on Linux​

You need to install LibreOffice https://www.libreoffice.org/download/libreoffice-fresh/. The steps to do this are outlined in this section.

ORA-31011: XML parsing failed, ORA-19202: Error occurred in XML processing, LPX-00651: VM Stack overflow​

You need to install an additional patch for APEX 5.0.4 which will fix this issue. Search in https://support.oracle.com for PSE 24341756.

Error occurred while acquiring license​

You received: "Error occurred while acquiring license. Please make sure that your API key is correct and that you have enough printing credits. Contact AOP if the problem persists."

This means you ran out of credits. Go to https://www.apexofficeprint.com and upgrade your package or send an email to info@apexofficeprint.com to see what we can do for your case.

SyntaxError: unexpected token P in JSON at position 0​

If you are using APEX 5.1, the Dynamic Action plug-in will always work, whereas the Process plug-in might give this error. When you put the process in the Processing part, it will only work if the “Reload on Submit” attribute (of the page) is set to “Always” (note this attribute is new in 5.1). This is due to a change how APEX 5.1 is handling Page Processing. If you would import an APEX 5.0 app in 5.1 by default it’s set to Always reload on submit, but if you create a new app in 5.1 it’s set to “Only for Success” and then the process plug-in is not working. Alternatively, you can put the AOP Process to be After Header and make it conditional, it will then work regardless of the setting of "Reload on Submit".

Chinese and other language and font support​

If you need special characters or language support, make sure the necessary fonts and languages are on your system. For example to add Chinese support on RHEL Linux do:

    yum install "@Chinese Support"

Further more if you want to install additional fonts here's a good link. AOP Cloud API has Google Noto fonts installed.

Installing a font is nothing more than installing the font on your system. For example on (RedHat) Linux, we copy the *.ttf files (or directory) to /usr/share/fonts/ and run "fc-cache -f -v"

Depending if you have a GUI (Linux/Windows) you can just double click on the font and it will install it in your system. If your system recognises it, MS Office or LibreOffice should be able to use it for the PDF conversion.

For barcodes, you could also choose to install a barcode font, for example Free 3of9 or http://www.dafont.com/3of9-barcode.font. Barcode fonts are more performant than images.

If you are using the font awesome and using html tag or interactive reports/grid in Word, you will have to install the font-awesome desktop fonts in order to render the PDFs properly. (AOP cloud server has free versions installed.)

Running AOP installed in a single schema but shared across multiple schemas​

We typically install the AOP packages in all the schemas we want, so you can run different versions of AOP for the different schemas. But some people like to install AOP once and use it in other schemas and workspaces. This way they only have to maintain one version of AOP and accross all workspaces it's the same. You can install AOP in one schema and create public synonyms for the package aop_api_pkg and aop_plsql_pkg. Alternatively you can install AOP in one schema and create the different synonyms in the different schemas you want AOP to be available in. Note that the packages include AUTHID CURRENT_USER, which means the package runs with the rights of the caller, except for the AOP Modal plug-in.

-- public synonyms
create or replace public synonym aop_api_pkg for aop_api_pkg;
create or replace public synonym aop_plsql_pkg for aop_plsql_pkg;
create or replace public synonym aop_convert_pkg for aop_convert_pkg;
create or replace public synonym aop_settings_pkg for aop_settings_pkg;
create or replace public synonym aop_modal_pkg for aop_modal_pkg;

--these are also required since the packages are using "authid current_user"
create or replace public synonym aop_api_pkg for owner.aop_api_pkg;
create or replace public synonym aop_plsql_pkg for owner.aop_plsql_pkg;
create or replace public synonym aop_convert_pkg for owner.aop_convert_pkg;
create or replace public synonym aop_settings_pkg for owner.aop_settings_pkg;

-- grants
grant execute on aop_api_pkg to public;
grant execute on aop_plsql_pkg to public;
grant execute on aop_convert_pkg to public;
grant execute on aop_settings_pkg to public;
grant execute on aop_modal_pkg to public;

In case you want to run the package in the context of the owner, go to the package aop_api_pkg and aop_plsql_pkg and remove the AUTHID CURRENT_USER and recompile.

The requested URL has been prohibited​

If in APEX you force all outgoing connections to be HTTPS by setting: Manage Instance -> Security -> HTTP Protocol -> Require Outbound HTTPS -> No.

Make sure you're calling AOP also with HTTPS. If you're calling the AOP cloud https://api.apexofficeprint.com make sure to load the certificate in your database or setup a proxy on your end.

PDF output suddenly stopped working​

Probably a LibreOffice or MS Office process is stuck. A way to solve this is running a script that checks for long running processes and that kills those. If you're using the AOP Cloud, go to https://www.apexofficeprint.com, sign-in with your registered email address and navigate to Support on the menu to contact us.

Map or Gantt or Other div not in output​

In some occations divs are not screenshotted well enough, so they don't appear in the output. For example the JET Gauge might not be taken. We are looking for a workaround for a future release.

ORA-29273: HTTP request failed ORA-12535: TNS:operation timed out​

Please check if your firewall allows outgoing connection from the database to the AOP server. If you added the AOP port to the firewall rules, make sure to restart the daemon.

If you're using a proxy, make sure to specify the proxy in Shared Components > Application Definition Attributes > Proxy Server or specify the global variable in the AOP_API_PKG.g_proxy_override.

See also further in this doc at chapter 14 Debugging connections to AOP Server.

Font in PDF is different from the font in my Word template​

This is most likely due to the fact that the font you chose in Word, is available on your local computer, but not in the server. If you install that font also on the server where AOP is running, it should do the conversion well. See also Chinese and other language and font support.

The generation of my document is slow​

You would need to figure out where you lose time:

  • creation of the JSON: your SQL statement or other source is taking time to be transformed in JSON. You can trace what is going on by setting your page in debug mode (APEX) or running your SQL statement in a SQL window.

  • time needed on the server by AOP: you can trace that by enabling logging (only on-premise) Start the AOP server with --enable_printlog or --verbose, which will show the incoming requests.

  • network time: measure the time to travel from your database to our cloud or to your own on-premise install. If you would use the on-premise version and put AOP on the same machine as the database, it would be the fastest. Or if you could add it on another server where the network is fast, that should get rid of the transfer time.

If the conclusion is that everything is as fast as possible, we advise to run the reports that take a long time, in the background. You could also schedule the reports to run overnight and store the results in a table.

With some versions of the Oracle Database there's an issue with the Cost Based Optimiser. Till you upgrade your database, you might want to try to use /*+rule*\/ hint in your top level SQL statement.

Safe button in Components Settings not available​

Most likely you subscribed the plug-in from another place. Go to Shared Components > Plug-ins

  • Click on References (number) and copy your logic where you used AOP. This is so you don't have to recreate your logic later. Next, remove all the processes or dynamic actions that use the plug-in.
  • Now you should be able to go to Shared Components > Plug-ins > AOP and hit the Delete button so the AOP plug-in is removed.
  • Import the AOP plug-in again, but make sure you don't reference it from another app, so Import from file.
  • Use the logic from step 1 to recreate your call(s) to AOP.

sqlerrm: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00664: VM Node-Stack overflow​

The node stack of the XSL VM can be set with an event. It defaults to 300, the maximum is 4096. You can set it with:

alter session set events='31153 trace name context forever, level 4096';

In your APEX application you can set it through Shared Components > Security Attributes and in the Database Session part under Initialization PL/SQL Code you add:

begin
  execute immediate q'[alter session set events='31153 trace name context forever, level 4096']';
end;

ORA-06550: PLS-00201: identifier 'AOP_API_PKG.xxx' must be declared​

The APEX Plug-in can't find the AOP_API_PKG package. To fix the issue, run aop_db_pkg.sql (in the "db"-directory) in your Oracle Schema (SQL Workshop -> SQL Scripts -> Upload -> Select file and hit Upload)

ORA-31186: Document contains too many nodes​

When there's a huge amount of data (>2 million rows) Oracle might give this error. If this amount is really necessary to export, as a workaround we recommend splitting the query so there's less data in one document and then merge the documents together with AOP.

ORA-31061: XDB error: special char to escaped char conversion failed​

The data in the tables contains non-unicode characters. Most likely this is due to copy/paste of documents. It's best to correct the data in the database.

Some useful regular expressions:

  • to replace non-unicode characters: regexp_replace( COLUMN , '[[:cntrl:]]', '')
  • to replace double spaces and non printable characters like tabs and line feeds, by one space: column_name = regexp_replace(column_name , '[[:space:]]{2,}|[:print:]', ' ')
  • to remove the spaces at the beginning and the end of a field value column_name = regexp_replace(column_name, '^[[:space:]]{1,}|[[:space:]]{1,}$', '')

ORA-20001: Issue returned by AOP Service (REST call: 504): Unknown error. Check AOP server logs​

This means that your request was too big and the processing took longer that the gateway timeout. AOP API will end the connection after 5 mins. Workaround would be to split the request into multiple files and merge them back later. You could also go with AOP On-premise version where you do not have this limitation.

Error. Error. Or unknown error, even when running in AOP Debug mode​

You are hitting an Oracle Database bug 20370037. For Oracle DB 12.1 and 12.2 there's a patch available for DB 18 and higher not yet. A workaround is to provide the /*+rule*\/ hint in your SQL statement. Alternatively you can do:

alter session set "_optimizer_use_feedback"=FALSE;
alter session set "_optimizer_gather_feedback"=FALSE;
alter session set optimizer_features_enable='12.2.0.1';

ORA-00604: error occurred at recursive SQL level1. ORA-01031: insufficient privileges​

Some people experience this issue when running AOP after upgrading the Oracle Database to 18c and 19c. This error pops up because of a known bug in the Oracle Database when using the CURSOR syntax. This is bug 30655906 and a patch is available through Oracle Support p30655906_198000DBRU_Linux-x86-64.zip.

In case a patch can't be applied, you can also fix the issue by doing:

alter session set "_fix_control"='20648883:OFF';

This bug is also mentioned in the ORDS 20.2 release notes.

ORA-01031: insufficient privileges for queries​

This is a known bug (29049673) in the Oracle database and happens when CURSOR expressions are being used. In order to fix it add the following before the AOP call:

execute immediate 'alter session set "_fix_control"=''20648883:OFF''';

Excel: Conflicting loops​

When using a loop tag in Excel, the starting and closing tags create a block (rectangle). If the loop tag is vertical, you can expect the block to grow as follows:

As can be seen the content below the loop has been pushed down. The grown block pushes everything from the starting tag's column till the ending tag's column.

Introducing another loop below the existing loop might produce a conflict when only a part of the new loop block is pushed down.

This situation can be resolved fairly easily. Just make sure that the first loop pushes the second block entirely. This can be done by moving the end loop tag to the column equal to or greater than last column of second block as shown below:

Error: Request cancelled​

There is sometimes a request cancelled error because of the 180 second default APEX transfer timeout. To fix this, you can insert the following PL/SQL code into the Init PL/SQL section of the dynamic action and increase the value as per your requirements.

aop_api_pkg.g_transfer_timeout:=' 1800';