FAQ

PLS-00753: malformed or corrupted wrapped unit

There might be multiple reasons why you get this. There might be a permission issue, most likely on UTL_FILE. Another reason might be that during uploading of the script in SQL Workshop > SQL Scripts the File Character Set was not set to Unicode UTF-8.

Where do I get the SSL certificate of API server?

If you are using HTTPS (we always recommend you to do this) for connecting with the API server, you might get a SSL certification validation error:

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-29024: Certificate validation failure

In this case you will have to load the SSL certificate of the root certification authority, in our case that of Amazon, into your oracle wallet. You can get this certificate by visiting https://apexofficeprint.com/ and looking at the certificate information. Example firefox:

Do I have to install LibreOffice when we need Word and Excel output only?

No - LibreOffice or MS Office is only used when you want to convert from one format to another. So Word-Word or Excel-Excel is working without. But if you want to do for example Word->PDF you need LibreOffice or MS Office or another converter you prefer.

Can AOP print directly to a printer?

AOP 3.1 and above includes the ability to print directly to an IP Printer, as long as it's available from the server AOP is running on. If you want to print to a local printer, check the Sample app how to print to an inline region, by adding a little bit of JavaScript to the dynamic action you can let the browser come up with a print window automatically.

Can you repeat the Table header in Word and Excel?

Yes, this is a setting in Office. When you right click on your header row in Word, you can go to Table Properties - Row and check the "Repeat as header row at the top of every page". An example is given on page 113 of sample application.

Does AOP support Pivots in Excel?

Yes, you can use the transpose function in Excel to pivot your data. AOP can also loop vertically by using the {:tag}.

Why is there a difference between an output in Word and PDF?

When going from Word to PDF, we use an external converter: MS Office or LibreOffice. It might be that if your template was built on your desktop in MS Office (Word), but your server is using LibreOffice (Linux) a font is different or there are minimal changes. The easiest way to find out what the difference is, is to open the template in LibreOffice on your Desktop and see why LibreOffice is treating it different. In most use cases it works just fine going from one to the other, but sometimes it's in the details.

Can I install the APEX Plug-in somewhere so all my apps have access to it?

You can make the aop_api_pkg a public synonym for aop_api_pkg PL/SQL package in case you only want the package in a global schema. The Oracle APEX plug-in itself you would still need to import in every APEX application you want to use it in, but the plug-in can point to the public package.

Can I use dynamic page break in Word?

Yes. In some cases, you might want to start a new page for every record, for example for when creating certificates. You can use the {current_child_index} tag and an angular expression to find out the length of records/cursors. {current_child_index} will resolve to the current index of the record array, starting from 0. An example is shown in page 5001 of our sample application, use the spotlight seach with keywords 'Dynamic Page Break' to find it easliy.

Given the data:

select
  'file1' as "filename",
  cursor(
    select
      cursor(
          select
          c.cust_first_name as "cust_first_name",
          c.cust_last_name as "cust_last_name"
          from demo_customers c
      ) as "customers"
      from dual
) as "data"
from dual

You can use the following tag to insert a page break for every record except for the last.

{#current_child_index!=customers.length-1}
 -------------page break ------------
{/current_child_index!=customers.length-1}

Please note that if you wish to output to pdf, the same can be achieved by using the PowerPoint template and {!slides_loop} tag.

Is there a way to stop Word from replacing my "Straight Quotes" with "Curly Quotes/Smart Quotes"?

Yes. In order to stop word from replacing your straight quotes, click File, Options, Proofing, AutoCorrect, uncheck in Replace as you type "Straight quotes" with “smart quotes”.

Is there a way to stop Word from adding a blank space between brackets when I paste the tag name?

Yes. In order to stop word from adding a black space between brackets, click File, Options, Advanced, Cut, Copy and Paste, Use smart cut and paste “Settings” button, uncheck Adjust paragraph spacing on paste.

Can I adjust the width and height of interactive report/grid export in Word?

If you are exporting interactive grid, you can adjust the columns width as you like (due to meta data issue you will also have to change the width a bit for all columns.). The ratio is then automatically taken into account.

In interactive report, you can use the following in html expression.

<span data-aop-width-weight="2"></span>#COLUMN_NAME#

The default weight for each column is 1. Let's say you have 4 columns and you provide this html expression in the first column. This will double the size of the first column in comparison to the remaining 3.

The following formula is used to calculate the percentage of width a column gets:

(weight of column)/(total weight provided for all the columns) * 100.

Since version 19.1.4 AOP allows to specify the width in either px,in,cm,em or pt. Use the following HTML EXPRESSION:

<span data-aop-width="10px"></span>#COLUMN_NAME#

If AOP detects this in one of the columns, the columns which do not have this expression will get the minimum width specified.

How can I show custom success and error messages using AOP Dynamic Action Plug-in?

You can defines another TRUE action before the AOP Plugin is triggered and overwrite the showSuccessMessage and showErrorMessage functions.

How can I make the words stick together (non breaking space)?

In Word / PDF output you might want to not let the data of a tag get broken between lines. In this case you can use the non breaking space. Below a select statement where the normal space character is getting replaced by a non breaking space:

select replace('Non breaking space',' ', unistr('\00A0')) from dual;

I have a restrictive network, does AOP cloud have static IP address that can be whitelisted?

Yes, we also provide a domain that has static IP address. Please use the following AOP URL:

https://static.apexofficeprint.com/api/

or

http://static.apexofficeprint.com/api/

and whitelist the following IP addresses:

13.248.209.166

76.223.73.125

PDF output through Excel template has different decimal separators (Windows)?

This is most probalby because of your region settings for the user running AOP. Please change the region settings accordingly. Hereby the steps to change the region settings for Service user.

How to get a download to Excel button in the Actions menu of an Interactive Report / Interactive Grid?

The below steps describe how to export an Interactive Report/Grid to Excel (XLSX) with AOP via the native IR/IG download dialog. The changes are done on the global page and works for all IR/IG of the app without the need of creating a DA on each page for each report, so reducing a lot of manual work.

Create a JS file with the following content and upload/reference it app-wide:

function addAOPXLSXDownloadToIR(pStaticIdItem) {
  // hack into dialogopen event of jquery ui dialogs
  $('body').on('dialogopen', function(event, ui) {
    var dialogWindow$ = $(event.target);
    // only process if it´s an IR download dialog
    if (dialogWindow$.parents('div.a-IRR-dialog--download').find('span.ui-dialog-title').text() === apex.lang.getMessage('APEXIR_DOWNLOAD')) {
      // get ID/static ID of IR
      var currentRegionId = dialogWindow$.attr('id').match(/(.+)_dialog_js/)[1];
      if (currentRegionId) {
        // build html markup for XLSX download button
        var html = apex.util.htmlBuilder();
        html.markup('<li')
          .attr('class', 'a-IRR-iconList-item')
          .markup('>')
          .markup('<a')
          .attr('class', 'a-IRR-iconList-link')
          .attr('href', 'javascript:$s("' + pStaticIdItem + '","' + currentRegionId + '")')
          .attr('id', 'download_excel_aop')
          .markup('>')
          .markup('<span')
          .attr('class', 'a-IRR-iconList-icon a-Icon icon-irr-dl-xls')
          .markup('>')
          .markup('</span>')
          .markup('<span')
          .attr('class', 'a-IRR-iconList-label')
          .markup('>')
          .markup('XLSX')
          .markup('</span>')
          .markup('</a>')
          .markup('</li>');
        // append html
        $('.a-IRR-iconList').append(html.toString());
      }
    }
  });
}
function addAOPXLSXDownloadToIG(pStaticIdItem) {
    $('body').on('dialogopen', function (event, ui) {
        var dialogWindow = event.target;
        // only process if it´s an IG download dialog and if not already added;
        if (dialogWindow.id.indexOf("ig_download_dialog") !== -1 && $(dialogWindow).find('#download_excel_aop').length == 0) {
            // get ID/static ID of IR
            var currentRegionId = dialogWindow.id.match(/(.+)_ig_download_dialog/)[1];
            if (currentRegionId) {
                // build html markup for XLSX download button
                var html = apex.util.htmlBuilder();
                html.markup('<li')
                    .attr('data-value', 'XLS')
                    .attr('class', 'a-IGDialog-iconList-item a-IconList-item')
                    .markup('>')
                    .markup('<span ')
                    .attr('class', 'a-IGDialog-iconList-link')
                    .attr('onclick', 'javascript:$s("' + pStaticIdItem + '","' + currentRegionId + '")')
                    .attr('id', 'download_excel_aop')
                    .attr('aria-label', "XLS")
                    .attr('title', "XLS")
                    .markup('>')
                    .markup('<span')
                    .attr('class', 'a-IGDialog-iconList-icon a-Icon icon-ig-dl-xls')
                    .attr('aria-hidden', 'true')
                    .markup('>')
                    .markup('</span>')
                    .markup('<span')
                    .attr('class', 'a-IGDialog-iconList-label')
                    .markup('>')
                    .markup('XLSX')
                    .markup('</span>')
                    .markup('</span>')
                    .markup('</li>');
                // append html
                $('.a-IGDialog-iconList ul').append(html.toString());
            }
        }
    });
}

Create page 0 items for holding the IR static ID:

P0_AOP_IR_STATIC_ID
P0_AOP_IG_STATIC_ID

Create a page load DA (Execute JavaScript Code) on page 0 to call above JS function

addAOPXLSXDownloadToIR('P0_AOP_IR_STATIC_ID’);
addAOPXLSXDownloadToIG('P0_AOP_IG_STATIC_ID’);

Create a page 0 DA (On Change of “P0_AOP_IR_STATIC_ID”) which calls the AOP plugin.

  • 1 True Action (AOP plugin)

  • 2 True Action (Execute JavaScript - To close the IR download dialog after download)
var staticId = $v('P0_AOP_IR_STATIC_ID');
$('div#' + staticId + '_dialog_js').parent().find('button').click();

For APEX 20.1 use:

var staticId = $v('P138_AOP_IG_STATIC_ID');
$('div#' + staticId + '_ig_download_dialog').parent().find('button.ui-dialog-titlebar-close').click();

Now again create page 0 DA for IG (On Change of "P0_AOP_IG_STATIC_ID") which calls the AOP plugin by changing the P0_AOP_IR_STATIC_ID to P0_AOP_IG_STATIC_ID where necessary.

Note: every IR/IG should have a static ID.

That´s it, the final result looks like this (on every IR/IG in the app):

How to setup a local Oracle XE Database, APEX, ORDS and AOP?

We love to use the Vagrant boxes provided by Oracle to setup a local instance quickly.
Vagrant provides an easy and fully automated way of setting up a developer environment. In conjunction with Oracle’s VirtualBox, Vagrant is a powerful tool for creating a sandbox environment inside a virtual machine. Getting started with VirtualBox and Vagrant is quick and easy!

Steps required to get Oracle Application Express up and running are:

git clone https://github.com/oracle/vagrant-boxes/tree/master/OracleAPEX
cd vagrant-boxes/OracleAPEX
cat README.md
cp /Users/YOU/Apex/db_express_edition/oracle-database-xe-18c-1.0-1.x86_64.rpm .
cp /Users/YOU/Apex/zip/apex_19.1_en.zip .
cp /Users/YOU/Apex/zip/ords-19.1.0.092.1545.zip .
vagrant up
vagrant ssh
vagrant upload AmazonRootCA1.cer /home/vagrant/AmazonRootCA1.cer
vagrant upload Amazon.cer /home/vagrant/Amazon.cer

vagrant ssh
orapki wallet create -wallet /opt/oracle/admin/XE/ -pwd YourWalletPasswd -auto_login
orapki wallet add -wallet /opt/oracle/admin/XE/ -trusted_cert -cert "/opt/oracle/admin/XE/Amazon.cer" -pwd YourWalletPasswd
orapki wallet add -wallet /opt/oracle/admin/XE/ -trusted_cert -cert "/opt/oracle/admin/XE/AmazonRootCA1.cer" -pwd YourWalletPasswd

sqlplus / as sysdba
alter session set container=XEPDB1;
select apex_web_service.make_rest_request('http://api.apexofficeprint.com','GET') from dual
select apex_web_service.make_rest_request(
        p_url         => 'https://api.apexofficeprint.com',
        p_http_method => 'GET',
        p_wallet_path => 'file:/opt/oracle/admin/XE/',
        p_wallet_pwd  => 'YourWalletPasswd') as r
  from dual