Wednesday, September 25, 2019

Free Oracle Cloud: 8. Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text

This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

In the previous posts we setup our Always Free Oracle Cloud machine and an Autonomous Database with Oracle Application Express (APEX). In this post, I want to show you how to get started with the popular printing and reporting engine, APEX Office Print (AOP). The AOP software makes it super easy to export your data into a nice looking PDF, a custom Excel file, a fancy Powerpoint or other output formats of your choice, just the way you want it.

AOP is being used by many customers, even Oracle internally, to export their data in the format they want. The data can come from the database, a REST or GraphQL web service, or even components like the Interactive Report/Grid from Oracle APEX. Although AOP works with any technology, it is most known in the Oracle APEX community as it's the easiest and most integrated print engine for Oracle APEX. You create a template in DOCX, XLSX, PPTX, HTML or TEXT, specify the data source, and tell AOP in which format you want the output to be (PDF, Excel, Word, Powerpoint, HTML, Text) and AOP will do the rest! You can find more information in this presentation about AOP.

Christina Moore of Storm Petrel wrote me a few days ago following: "We have a client in one of our systems who generates a 1,888-page invoice monthly (about 2,000 pages). The most recent invoice was $1.3M USD and took 384MB. AOP handles it brilliantly. Well done. I can’t email it to you for confidentiality reasons, but know it has multiple sections that are merged with your tool too." I love feedback on the usage of AOP and am amazed how creative people are when developing with AOP!

I use AOP in every project because exporting/printing is a requirement sooner or later and an essential part of my Oracle APEX apps. So I thought to write how to use this in the Oracle Cloud :)

We have two options: we let our Oracle Autonomous Database and APEX talk to the AOP Cloud or we install an on-premises version of AOP on our own Compute VM. 

Ok, so let's get started...  open a browser and go to https://www.apexofficeprint.com and click the SIGN UP button:


Enter your email and hit Signup:


You will receive an email. Push the Confirm your email address button:


The browser will open where you can set a password for your account:


After hitting the Set Password button, you are logged in automatically and will see a Getting Started wizard:


Follow the wizard and you are all set! It should take less than 15 minutes :)

In short this is what the wizard will tell you:

  1. Download the AOP software and unzip the file
  2. Go to APEX > SQL Workshop > SQL Scripts > Upload and Run the file aop_db_pkg.sql which you find in the db folder. This will install the AOP PL/SQL API.
  3. Go to APEX > Your APP > Shared Components > Plug-ins and Import the APEX Plug-ins you find in the apex folder.
  4. Go to APEX > Your APP > Shared Components > Component Settings > APEX Office Print (AOP) and enter your API Key which you find in the Dashboard on the AOP site: 



The Component Settings in your APEX app:


The above is to configure APEX Office Print (AOP) in your own app using the AOP Cloud.

When you look closely at the previous screenshot of the Component Settings, look at the AOP URL.
The URL specifies where the AOP Server is running, which the AOP APEX Plug-in and AOP PL/SQL API communicate with. By default this is set to the AOP Cloud, so you don't have to set up an AOP Server in your own environment.

On-premises version of AOP

Although the AOP Cloud is really convenient as it's maintained and support by the APEX Office Print team, some customers prefer to run the AOP Server on their own machine, especially when data can't leave the datacenter.

So if you read on, I will walk you through Setting up the AOP Server on your own Compute VM in the Oracle Cloud.  Just be sure you have already installed the AOP Sample Application, plug-ins, and Database Objects, if needed, as instructed in the Getting Started section, above.

From a Terminal connect to your Oracle Cloud VM:

ssh -i ssh_key opc@public_ip

The first thing we do is change to the root user, as we want to install some supporting objects for AOP it will be easier to do it with the root user. Alternatively, in front of every command, you can add sudo.

We logged in as the OPC user, to become the ROOT user we do:

sudo su

Unlike other reporting engines, AOP software exists only out of a couple of files and is installed in no time. We will download the software in the tmp folder on our machine and unpack it in /opt/aop:

cd /tmp

wget https://objectstorage.us-ashburn-1.oraclecloud.com/n/id9u4qbhnjxj/b/apexofficeprint/o/aop_free_oracle_cloud.zip

unzip aop_free_oracle_cloud.zip -d /opt/aop

That's it!! The AOP Server is installed!



To support PDF output, AOP relies on a 3rd party converter like MS Office or LibreOffice. Here are the steps to install LibreOffice:

yum install java-1.8.0-openjdk.x86_64

yum install cups.x86_64

wget http://ftp.rz.tu-bs.de/pub/mirror/tdf/tdf-pub/libreoffice/stable/6.2.7/rpm/x86_64/LibreOffice_6.2.7_Linux_x86-64_rpm.tar.gz

tar -xvf LibreOffice_6.2.7_Linux_x86-64_rpm.tar.gz

cd /tmp/LibreOffice_6.2.7.1_Linux_x86-64_rpm/RPMS/

yum localinstall *.rpm

ln -s /opt/libreoffice6.2/program/soffice /usr/sbin/soffice

Note: if one of the above commands fail because yum is locked, you can remove the lock with: yum versionlock clear

LibreOffice is installed. To see if everything is fine you can run "soffice --version" and you should see something like this:



AOP comes with a built-in web server. When you start AOP you can define the port where AOP will listen to incoming requests. The default port is 8010. We will need to tell Linux this port can handle HTTP and HTTPS requests.

semanage port -a -t http_port_t  -p tcp 8010

To start AOP on the default port do:

cd /
./opt/aop/v19.2.3/server/APEXOfficePrintLinux64 --enable_printlog &

You should see something like this:



Yay!! AOP is running.

AOP comes with a cool Web Editor, we will make this Editor available on our domain dgielis.com/aop/. In order to do that, we will adapt Nginx to also be a reverse proxy for the AOP Web Editor. 
Here we go; 

vi  /etc/nginx/conf.d/dgielis.com.conf

And add the following section:

  location /aop/ {
    proxy_pass http://127.0.0.1:8010/;
  }

The server part of the config file becomes:



We need to reload Nginx:

nginx -s reload

And now when we go in a browser to dgielis.com/aop/ we see the AOP Web Editor:



You can now, for example, load a sample by clicking the "Load sample" button and select PDF.
Scroll down a bit lower and click the Process button and a PDF is being generated :)



The Web Editor is built in React.js and you can drag-drop your template and add some data to test the features of AOP. There's also a Logging tab (toggle between Editor and Logging), so you can see incoming requests, results and debug output in case of errors.


Now if we want to tell our Oracle APEX apps to use our own AOP Server, the only thing we have to do is change the AOP URL.

In your Oracle APEX app, go to Shared Components > Component Settings > APEX Office Print (AOP) and change the AOP URL to the URL of your own Compute VM:


That's it! You are all set to print and export data within your own environment :)

I would recommend looking at the AOP Sample App which you installed in the last step if you followed the Getting Started wizard. It will show over 500 examples of how to use AOP and its features!

Now I hope you enough knowledge so that you can please your customers with nice looking PDF, Excels and other documents in the format they want.


In the next post, we will add an Object Storage to our Always Free Oracle Cloud Plan so we have a place to store files and backups.

26 comments:

Norman said...

Thanks.useful content.

Carsten Cerny said...

Hi and good morning,

I tried to install the sample app from AOP (aop_sample_apex_app.sql) in my Oracle VM but I got an error. At the end of "Install Summary" is written:

ORA-24344: success with compilation error
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190100", line 592
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_190100", line 578
ORA-06512: at "APEX_190100.WWV_FLOW_DYNAMIC_EXEC", line 2033

What did I wrong?

Dimitri Gielis said...

Hi Carsten,

I believe your error is with the Supporting Objects?
If you look into the details, does it say in which script it was?

The Supporting Objects will install the AOP PL/SQL API and some sample packages. Those sample packages use some tables from the Sample Database Application. Is it possible that this application was not installed yet? If you install the Sample Database Application, you should be able to recompile the AOP packages.

Feel free to send us an email at support@apexofficeprint.com and we can do a quick session with you too, to make sure all is up-and-running on your side :)

Hope that helps,
Dimitri

Carsten Cerny said...

Hi Dimitri,

The point with the Sample Database Application was the right one. After installing that I could install the AOP Sample app.

Thanks for that,
Carsten

Marc said...

Hi Dimitri,

I think I followed all the posts up to this one to the letter. They all work (thank you for that!).

But I am having issues with this one, the AOP step.
After creating the package and the DA's, I tried to create my first print, but I got this error:
ORA-20000: Issue calling Main AOP Service (REST call: ):
ORA-20987: The requested URL has been prohibited. Contact your administrator.

Maybe the problem lies in the region, I entered the closest to home, Frankfurt.

Thanks in advance

Dimitri Gielis said...

Hi Marc,

If you go to Shared Components > Component Settings > APEX Office Print
Make sure the URL you use is HTTPS://api.apexofficeprint.com (or your own AOP Server, but it has to be https)

Hope that helps,
Dimitri

Marc said...

Hi Dimitri,
Thanks for the quick response.

After adding the s , (I overlooked your clear instruction) something did change.

A different error, but, now in my own language.

AOP downloadAOPFile Error ORA-20000: Issue calling Main AOP Service (REST call: ):
ORA-29273: HTTP-aanvraag is mislukt.

Dimitri Gielis said...

Hi Marc,

That is a strange error. It looks like an ACL issue or firewall issue.
Does your instance can connect to the internet?

In SQL Workshop can you try:
select apex_web_service.make_rest_request('https://www.google.com','GET')
from dual;

If you get the same error it means your user can't go outside... you want to check your firewalls then.

Dimitri

Marc said...

Thank you Dimitri,

The statement returned no error, so the only thing I could think of was to delete the plugins and retry.
That fixed it.

thumbs up!

Marc

Carsten Cerny said...

Hello,

all was working with AOP. After a few days I tried to access via https://my_domain.com/aop and got an bad gateway error from nginx.

The AOP server wasn't running anymore. I started it again with

./opt/aop/v19.2.3/server/APEXOfficePrintLinux64 --enable_printlog &

It's running again now but I see the following message on the console:


Could not find the license file. Expecting it to be in /opt/aop/v19.2.3/server/aop.license.
If your license file is somewhere else please give in the location with --license argument.
Trial has been enabled. Activate the software with '--activate' or '-a' option


I got the license key and put this in the shared components part but I didn't receive a license file. And how can I check why the AOP server was stopped?

Best,
Carsten

Dimitri Gielis said...

Hi Carsten,

What URL do you use in APEX > Shared Components > Component Settings?
if this is https://api.apexofficeprint.com, you are connecting to our Cloud and you don't need to run the executable.
Our own Cloud will check the API key you provide.

Alternatively, If the URL specified is your own domain/aop url it means you connect to your own AOP Server. Then you need a license file, otherwise, the AOP Server will run in Trial Mode. The license file can be generated by specifying -a (to activate AOP). Note you would need to have bought the AOP Server in order to activate.

Hope that clarifies,
Dimitri

Carsten Cerny said...

Hello Dimitri,

Thanks for your answer. I followed your post about the AOP installation and changed the URL at the end to my own domain (Oracle VM instance).

If I get you right: I have to pay if I will use AOP on my own installation. Otherwise I can use https://api.apexofficeprint.com with my API key for free? That was not clear for me reading your blog post.

Best,
Carsten

Dimitri Gielis said...

Hi Carsten,

> If I get you right: I have to pay if I will use AOP on my own installation. Otherwise I can use https://api.apexofficeprint.com with my API key for free? That was not clear for me reading your blog post.

Sorry if it was not clear.
- With https://api.apexofficeprint.com we offer the FREE AOP Cloud (30 reports/month every month for ever and unlimited dev credits) If you need more, there are paid plans too.
- With the on-premises AOP version you have unlimited DEV reports (basically all functionality is there, but there's a small watermark). If you don't want the watermark, you can buy an on-premises version.

Hope that explains better.

Thanks,
Dimitri

Carsten Cerny said...

Hello Dimitri,

Now it's clear for me. Thanks for explanation.

Have a nice weekend,
Carsten

juanjf9 said...

Hi Dimitri, I'm using an ORACLE Cloud instance (VM) to install the on-premise AOP server.

I followed your steps until YAY! AOP its running.

but then I'm not able to modify any conf file.

I have installed the AOP 19.3 Sample Application and changed AOP URL to my VM public IP Address.

when testing I get the following:
Contacting AOP Server failed!
< Please check if you ACL has been configured properly.
Error:
ORA-29273: HTTP request failed ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1036 ORA-12535: TNS:operation timed out ORA-06512: at "SYS.UTL_HTTP", line 639 ORA-06512: at "SYS.UTL_HTTP", line 1415 ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 934 ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1566 ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 408

Any advice!

Dimitri Gielis said...

Are you sure you use the HTTPS URL of your VM?

You can't really use the IP address, as Oracle doesn't allow outgoing HTTP requests, they have to be HTTPS.

see this post how to setup HTTPS and get your domain URL pointing to your VM (AOP instance): https://dgielis.blogspot.com/2019/09/free-oracle-cloud-7-setup-web-server-on.html

Hope that helps,
Dimitri

juanjf9 said...

Thanks a lot for the quick reply.

I will try this and let you know.

Regards.

Unknown said...

Hi Dimtri,

Great work for speeling out AOP on Oracle cloud services. I haven't installed the VM just the AOP on APEX. I get the following Oracle error.

ORA-20000: Issue calling Main AOP Service (REST call: ): ORA-20987: The requested URL has been prohibited. Contact your administrator.

I know this is an Oracle issue as I have another AOP running on Digital Ocean without any issues. Any help is appreciated.

Thanks

Hazi

Unknown said...

Hi Dimtri,

Ignore my previous comment. Your examples are with http rather than https

It works

Hazi

Yvo Breuer said...

In case you encounter the error 413 Request Entity Too Large when importing the AOP Sample APEX app you have to adjust the client_max_body_size setting in the file /etc/nginx/nginx.conf. In the previous step you've probably set this to 50M, but aop_sample_apex_app.sql is 66M. I've adjusted it to 100M and the error was gone.

Holger Lehmann said...

Hi,

https://objectstorage.us-ashburn-1.oraclecloud.com/n/id9u4qbhnjxj/b/apexofficeprint/o/aop_free_oracle_cloud.zip is invalid in the meantime.

Mukesh Oracle Apps DBA said...

Hi ,

I have upgrade APEX from 5.1 to 21.1.

Now I am trying to upgrade AOP from 3.0 to 21.1. I am performing below steps.

1. Download cloud package of AOP 21.1
2. Unzip AOP 21.1 on local machine
3. Upload aop_db_pkg.sql - navigate to SQL Workshop -> SQL Scripts -> Upload.

But I am getting below error while uploading aop_db_pkg.sql , please help to solve this.

"Error processing Request , Contact your application administrator"

Please help how to solve this issue.

Dimitri Gielis said...

Hello, Can you please contact support@apexofficeprint.com

I encourage you to run in APEX Debug mode and see what the exact error is.

Dimitri

Femi said...

Hello Dimtri.

Thank you for your guide on how to install AOP.

Using the aop_win_v21.1.2 zip file, I attempted to install AOP Windows version on the Apex installation running on my laptop.

Two out of the nine statements failed as shown below:

create or replace package body aop_api21_pkg wrapped a00000 Error at line 0: PLS-00753: malformed or corrupted wrapped unit

create or replace package body aop_convert21_pkg wrapped a0 Error at line 0: PLS-00753: malformed or corrupted wrapped unit

How should I proceed from here?

Regards,
Femi

Dimitri Gielis said...

Hi Femi,

Most of the time it means there are not enough grants
- GRANT EXECUTE ON SYS.UTL_HTTP TO your_schema;
- GRANT EXECUTE ON SYS.UTL_FILE TO your_schema;

Next try to run aop_db_pkg.sql again in SQL Workshop > SQL Scripts

Hope that helps,
Dimitri

Martin said...

Hi Dimitri,

I'm working with a small charity and we are using the AOP Cloud service with a free subscription.

I'm getting the malformed or corrupted wrapped unit when installing AOP 24 on a new Oracle 23ai instance hosted on OCI. I've granted execute on UTL_HTTP and UTL_FILE. I've also ensured that the aop_db_pkg.sql gets uploaded as Unicode UTF8 when loading the script to Apex.

Hoping that you might see this and give me some pointers on what I might be missing.
Thanks
Martin