Thursday, April 29, 2021

Setup the APEX Office Print (AOP) Server with the Oracle Cloud APEX Service

This post is part of the series My spin on the new Oracle APEX Application Development Service.

To print and export data from the APEX Service in the Oracle Cloud, you can use APEX Office Print (AOP).

AOP allows you to generate documents based on templates created in Word, Excel, PowerPoint, HTML, and Text.
For example, people use AOP to create an invoice, send a packaging slip, export an Excel spreadsheet with multiple sheets, or have a monthly PowerPoint presentation with the latest sales numbers.

AOP the go-to print engine for Oracle APEX and the Oracle database and it offers both a cloud service and an on-premises solution. Having your APEX Service talk to the AOP Cloud is straightforward as there's really no installation to do. You can find more info about that in this blog post. But, what if you want your own dedicated AOP server for your APEX Service? Many people actually like to have their own instance so they can print as much as they want, have full control, and maximum speed - as it's closest to your database. In this post, I will show one way to set up your own APEX Office Print (AOP) Server which works with the Oracle APEX Application Development Service.

The special bit with the Oracle APEX Service is that it's a fully managed service, which means the database, ORDS, and APEX are pre-configured, auto-patched, auto-scalable, and optimized. I think it's awesome, but the downside is that you don't have direct access to the machine and the network. 

The installation is a 3 step process:
1. Import the AOP Server image in your own Oracle Cloud
2. Create a Compute instance based on this image
3. Create a Gateway to this instance

The following is a step-by-step guide:

(1) Login into, go to the Downloads section, and click AOP Gateway with Compute 
This will copy the URL of the AOP image, which we will import in your own Oracle Cloud. (FYI we are focussing heavily on the ability to let you run your own AOP instance in the Oracle Cloud, so in the Oracle Cloud section on our website, you will find more ways to run AOP).

Go to your own Oracle Cloud dashboard and click Compute - Custom Images:

Click the Import Image button:

Give it a name and copy the link that is in the clipboard to the Object Storage URL:

Hit the Import Image button:

(2) It might take a few minutes in order for it to create the image, but once it's green, we can Create Instance from this image:

Give the instance a name, select where you want the Compute instance to be, and select an Image and shape:

The selection of the shape really depends on how much you print. I like, for example, the flexible shape
AMD Rome: VM.Standard.E3.Flex (2 OCPU, 20GB Mem) which will cost around $60 a month.

Select the network and assign a public IP address. Note that the public IP is not really necessary, but it just makes the activation of AOP easier as you can connect directly to the Compute instance:

Finally hit the Create button:

Now the Compute Instance will be created:

A few minutes later, the pre-configured AOP instance is up and running. 
Write down the Private IP Address and Click on Subnet:

Next click on the name of the Security Lists:

And Add Ingress Rules:

Add the port AOP is running on. By default, AOP is running on port 8010, but you could change that in the instance if you wanted, and hit Save Changes:

The Compute instance is now fully configured.

(3) The issue with the APEX Service is that it only allows HTTPS calls. Instead of dealing with SSL certificates, we will make use of the Gateways feature in the Oracle Cloud which you find under Developer Services - API Management - Gateways

Create Gateway:

Give it a name and hit Create:

Once the Gateway is created go to the Deployments section:

Create Deployment:

Give it the name AOP and path prefix /aop and hit the Next button

The path can be / and for the URL use the Private URL of the Compute instance.
Make sure to check the "Disable SSL Verification". Our Compute doesn't have SSL, but the Gateway does and the APEX Service will only talk to the Gateway.

In the overview screen hit the Create button

And you will see that AOP is being deployed and becomes Active:

Click the Copy Endpoint in the Deployments section - this is your AOP Server URL!

Those are basically the steps to get your own AOP Server running with the APEX Service.
In fact, it's not only linked to the APEX Service, you can use this technique for any Oracle Cloud service, like Autonomous Database or your database on another Compute instance.

To manage the AOP Server here are some commands you can use when connected to the AOP Server:

start aop : systemctl start aop stop aop : systemctl stop aop status aop : systemctl status aop autostart at boot : systemctl enable aop remove autostart at boot: systemctl disable aop

Happy printing!

Thursday, April 22, 2021

Which one to pick: Free APEX Service vs Free Oracle Autonomous DB

I'm a big fan of the Free Oracle Cloud as it allows Oracle APEX, Oracle Database, web developers, or more generally, everybody, to get started doing their thing. If you want to get going, I previously did a series you may want to read called Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

A few days ago Oracle announced the free offering for the Oracle APEX Service. I previously started on a series on this new APEX service My spin on the new Oracle APEX Application Development Service. I still have to continue that series, but I first thought to write now that it's available for free, about which one to pick.  The Free Oracle Autonomous DB which comes with APEX or the Free APEX Service which comes with the Oracle DB?

All-in-all both services are very similar, but the decision is very easy for me. Ask these two questions to yourself:

Do I want to have SQL*net access (e.g. connect with SQLcl, SQL Developer, or Visual Studio Code)?

If the answer is yes, you need to go with the Oracle Autonomous Database as of the time of writing SQL*net access isn't available for the APEX Service.

Do I want to upgrade my free service to a paid service at some point?

The APEX Service is unbelievable value for money, and 1/3 of the price of the Autonomous Database, so if you ever want to upgrade in one click to a paid version of the APEX Service, you want to start from the free APEX Service, You can't go from the Autonomous DB to the APEX Service, so the one you pick decides where you upgrade to.

Combining both ...

If you don't mind a bit of hassle, you could start with the Always Free Autonomous Database, export the database and APEX apps, and import them into the APEX Service. It will take a bit of effort to do this, but it's definitely a good choice if you don't mind doing that. But from that moment onwards you will work with the APEX Service... or you treat the APEX service as your production environment and your Always Free Oracle Autonomous DB as your development environment.

If you wonder why do you even care about SQL*net access... I work file-based, e.g. all my PL/SQL objects (packages, procedures, ...) are in files in Git and I edit them, I see very nicely what I changed, I can easily commit, etc. I won't mess up other people's code and I can always go back to a previous version. So, I really like to work in Visual Studio Code linked to my Git. Now I could just copy/paste when I'm done editing and compile it in SQL Workshop of SQL Developer Web, but it's one extra step.

Starting with the Always Free APEX Service and upgrading to Paid.

For most smaller projects I will probably educate people to go with the Always Free APEX Service. Most time is spent in APEX anyway and SQL Workshop or SQL Developer Web is good enough for those small projects to maintain the database objects as typically not many people are working on the same package at the same time. So I thought to see how well upgrading from Free to Paid would work... and I must say I was impressed! It took about 15 minutes to do. Here's what I did:

In the Oracle Cloud console, go to the APEX Application Development - APEX Instances and hit the Create APEX Service button:

Follow the wizard to Create the APEX Service; give it a name

Add a password and provide the email addresses of people you want to be notified of maintenance. This notification part is new and really useful!

The Always Free APEX Service is ready and you can play with it. Now, let's hope at some point you are so successful with your APEX app, that you will switch to a Paid instance. Here's what it takes to upgrade.
Click the More Actions : Upgrade Instance to Paid:

Confirm you want to upgrade:

It will take a few minutes to update:

And presto, there it becomes available:

Easy! Now one of the best features of the APEX Service is that it can Auto Scale, which means that on heavy traffic the service can take up to 3 times the OCPU you have. I find that super cool :) You pay for it, but you don't have to do anything, and it's only for the time it needs more resources.

When you upgrade from a free tier, the auto-scaling is not enabled by default. Here's how to enable it: Click More Actions > Scale:

Hit the Auto Scaling checkbox:

This takes a bit of time and for me, it didn't automatically tell me it was done....

... at some point I refreshed the browser and saw it was done:

I like the APEX Service a lot. It's missing a few features before I can move a few customers
e.g. the ability to have custom domain names to name one. 

But since January 13th I'm actually using the new APEX Service and hosting a public website on it.
It's a fun app to predict the curve of the COVID-19 cases in Belgium. There's a static page in front of it, so the custom domain name is there, but once you click on Predict (Voorspel zelf), you will see the APEX app hosted on the APEX Service.

Hope this post helps to pick the Free Oracle Cloud service you need.