Feed aggregator

Today United Codes changes the way you look at your Oracle APEX apps

Dimitri Gielis Blog - Wed, 05/20/2020 - 13:24
This is a day I've been looking forward to for two and a half years.

Every once in awhile, a revolutionary product comes along that changes everything ...

... and United Codes has been very fortunate. It's been able to introduce a few of these into the Oracle APEX world.

In 2015, we introduced APEX Office Print; it didn't just change the way we export and print data; it changed the entire mindset on how to present data.

In 2019, we introduced APEX Media Extension; it didn't just replace Oracle Intermedia; it changed the way we deal with media in APEX.

Well, today, we are introducing three revolutionary products for Oracle APEX:
  1. an automated documentation generation tool
  2. a monitoring tool
  3. a quality assurance tool
An automated documentation generation tool, a monitoring tool, and a quality assurance tool ... these are not three separate products, this is one tool, and we are calling it ... APE - APEX Project Eye.

You might recognize the above ... this is how Steve Jobs announced the iPhone. For me, one of the best presentations I've seen. Of course, APE is not the new iPhone, but I strongly believe that APE will change the way we deal with Oracle APEX applications. Let me tell you why...

Automated Documentation Generation

Nobody likes to write documentation. In most cases, documentation is written at the end of a project and because someone forces you to do it. It takes days to write and to decide what to include and what to leave out. It's not always clear why you even write the documentation. Maybe it's because you want to remind yourself of some functionality. In most cases, however, you write it to help other people understand the project. The day it's written, it's already outdated, as things change. Documentation is hardly ever being maintained or even able to be located when needed. In short, it's just a pain.

When you follow a few good practices during development of your Oracle APEX app, APE will do the documentation part for you! You will have always easy to find and up-to-date documentation. The person looking at the documentation can decide what they want to see and what they want to dive deeper into.

You can define a project in APE and specify which applications belong to this project. You can specify some background of the project, but the real power is that APE reads the Oracle APEX meta-data. APE also allows you to change the help and comments directly inside the APE interface. If you follow the APE guidelines on how to write comments for your database objects and procedures and packages, you are golden. APE reads all that info too and presents in an easily digestible format.

And in case you want to export the documentation, you can do it with one click as we have integrated APEX Office Print (AOP) with it. Select the different sections you want in the document, chose the format (HTML, Markdown, PDF), and hit the print button. 


If I asked you, how many workspaces and applications do you have in your Oracle APEX instance? And from those applications, how many are currently in use, and who is using them? Are things running smooth or are your users receiving many errors? Would you know?

I didn't.  And almost all companies I'm called into have only a rough idea of what apps are running. In one company that I visited a few months ago, they asked me to document all the apps that were running and who was using them... the people who managed their Oracle APEX apps were replaced a few times, and they just had no idea anymore.

When you create an Oracle APEX app today, you can specify a feature called Activity Reporting, which gives you a decent overview of that specific application. But I always wanted more than that.

I wanted a holistic overview of my Oracle APEX instance. Show me in the entire instance -  what is going on, which workspaces and applications are still being used, how much, and by who. But also let me drill-down into a specific app and get detailed insight into which pages are being used the most, where errors are happening, and how the performance is. I'm not only interested to see numbers, but I want to know that today things are running better than yesterday or the week before. APE will show you exactly that, in a clean and easy to use interface.

This dashboard gave us so much insight into our own environment. It showed me not only do I have to fix some bugs in applications I wrote many years ago, but also that I might need to look at some queries as performance isn't always that good and probably is slowing down my database at times. It also showed me which applications are important and being used on a daily basis and by whom.

And that company who asked me to document their Oracle APEX environment? I just installed APE and the management now has an always up-to-date view of their environment.

Quality Assurance

You start the project by creating a data model. Next, you create the Oracle APEX app, show the customer, make changes in the data model, and the APEX app and you iterate like this a few times. At least this is how the start of a typical project looks like for me.  For other companies it might look a bit different, perhaps a bunch of consultants comes in, which work on an APEX app, do some handover, and leave.

The hardest part is keeping an eye on the relationship between the database objects and your APEX components. Which database objects are still being used? Which APEX pages are still being used? And oh boy... we are two years later and I have to make a change in this table, what's gonna break?

Another part I always found challenging is to know if how you built the application and intended it to be used, if that is also how people actually use the app. It was only by people showing me their workflow that I could effectively give them tips on how to better use the app... or actually, improve the app so it was more obvious for the end-user what was expected.

So what I understand under quality assurance tool; is a tool that helps you to code well, that gives you a clear view of the database objects, the APEX components, the relationship between them, and visibility in the usage of the app, so you have all you need to do an amazing job.

APE will not only give you insight into all the components but regardless of how you look at it, you get the picture! Either you start with the Entity-Relationship diagram and see the APEX components linked to it. Or you start with the Page Flow of your APEX app and drill back to the ERD. And finally APE has a visual that has never been done before, we call it the Session Flow. It's an amazing visual representation of how people actually use your app and how people navigate from one page to the other.

All of these views have given me insights into my own Oracle APEX apps, which I've never had before. We got insight into how people use our portals, we saw we had database objects which were not used at all and when we had to change a page, we knew what the impact would be on other pages linked to that page. It's an awesome feeling when you truly master your app.

APE - Your Second Screen

It's really cool to have an app with the above, but as developers, we are constantly in Page Designer when we create Oracle APEX apps, right? Wouldn't it be nice to have APE on a second screen and while you develop, APE gives you more information, like the database object dependencies of that page?

We thought that too ;) Click on the Eye in the Tree View and be amazed :)

The first time I saw the Live Insight feature, I couldn't stop smiling... you see instant database object dependencies, you see relationships of objects, links from other pages, it's all there. Seeing it is believing it!

Note: for some people, quality assurance is following naming conventions and good practices. And I love for example to use the APEX Advisor, as it does some checks for you. In a future release of APE, we also want to cover that area of QA and our plans are to include an advisor you can customize with your own checks.

And there's one more thing ...

We decided to make APE completely FREE for personal use! Head over to https://www.apexprojecteye.com and download APE today!

Let the EYE be with you! ~ Yoda

ORDS: The database user for the connection pool named |apex|rt|, is not authorized to proxy to the schema

Dimitri Gielis Blog - Mon, 05/11/2020 - 20:38
When configuring some Oracle REST web services through Oracle APEX, I got confused, so I thought to quickly write down, so I don't forget for the future.
Although I use many ORDS web services in several schemas, for a new schema I got following error: "The database user for the connection pool named |apex|rt|, is not authorized to proxy to the schema named ."

I was confused, as I enabled the schema to be REST enabled.

It was only once I gave following permissions it worked:

alter user {USER} grant connect through APEX_REST_PUBLIC_USER;

There you go, it might help you too :)

A new look, a new name...

Dimitri Gielis Blog - Sun, 05/10/2020 - 11:05
Last Thursday we gave a new look to our products APEX Office Print (AOP) and APEX Media Extension (AME). Not only did they get a new logo, but the whole website and portal were also put in a more modern, minimalistic look.

At the same time, we announced that our company APEX R&D would from now on, sail under the flags of United Codes.

With United Codes we want to bring all of our products and services together under this new brand.

In the coming weeks, we will announce two more products, which I can't wait to show you!
In our heart, we love the Oracle Database and Oracle Application Express (APEX), so both products will be in this area.

United Codes

This blog post isn't just to say we have a new logo and brand, but I want to share why I'm thrilled about our new brand "United Codes".

When I look at my life, there has always been a constant, I love to bring ideas into reality with code.

It can be something small, like when I was a teenager and I was in love with a girl instead of writing a card, I coded a program that would show her name and hearts. She never saw it, as it turns out she wasn't interested, but it gave me so much pleasure when I got the code to work.

Bringing ideas to life alone is nice, but doing it together with others is even nicer. Together you can do much more.

I remember a day at Oracle Open World (OOW) where there was a coding challenge. Initially, I wasn't going to participate, but the night before the deadline, Martin D'Souza and I said; "lets code together for one night and see where we end up". It wasn't the fact that we won which gave a lot of pleasure, it was working together to bring the idea we had to life.

Our AOP product is not any different. I had this idea to more easily get template-based reports out of Oracle APEX. I started on a prototype and got it to work, but it only became really awesome when more people started to work on it, especially Sunil Tandan. Not only did we work together on something, but it also became a shared dream.

In my consulting career in many cases the best results happened when there was a mixed team from business, coders, and people who just were passionate about bringing an idea to life.

The name UNITED CODES describes someone who is able to master the "code of the problem" aka the „input-code“ as well as the "code of the target" aka the „output-code“, and unites them by knowing the "code itself" which is the key concept and solution.

The logo is in principle the union of two opposites, as for example body and spirit find their union and center in the soul. At the same time, it can be read as a narrative from left to right and thus tells the story. As a whole, especially through its center, it can actually be seen as a heart, which is also the center in man and the seat of the soul, of enthusiasm and devotion. At the same time - at the level of the spirit - the center is an eye that unites opposites in its ability to see. Furthermore, the sign can also be seen more than twice as the letter "C", the initial letter of the word "code" denoting the "united codes".

There are many more codes than those of the programming languages used in computer science. Each area has its own code and each problem has to reveal its own code first before it can be recoded into a solution. Experts for the input codes may be mathematicians, engineers, system theorists, cyberneticists, game theorists, or physicists. Experts for output codes are computer scientists and full-stack developers as well as certain specialists for implementing the code.


What excites you for 2030? Did you already think about that?

What excites me is to bring ideas to life. In the short run I want to focus on Oracle Database and Oracle APEX, as in my view, it's one of the best tools to bring data and ideas to life. Oracle's mission statement resonates really well with me: "Our mission is to help people see data in new ways, discover insights, unlock endless possibilities."

When you saw my VR presentation which I did two years ago, I already expressed my dream. I really believe the low-code development is at the start, I envisage we can build apps, just like Tom Cruise used his hands in Minority Report to get insight into his data:

During my research in this area, I believe the technology isn't advanced enough to go mainstream.
Although SpaceX already used it to design rockets, for coding inside a browser the technology wasn't good enough yet. This would be one example of how to bring ideas to life faster, but there are many other ways to achieve that.

I really love a quote from the movie Ratatouille which I slightly changed: 
"Not everyone can become a great coder, but a great coder can come from anywhere"

Many people have great ideas but lack the ability to bring it to life. With United Codes we can accelerate bringing ideas to life.

I want to finish this post by thanking the entire team at APEX R&D for all their efforts the last years to bring us where we are today. I couldn't wish for a better team and I look forward to starting this United Codes journey with them.

Configuring AOP as Print Server in Oracle APEX 20.1

Dimitri Gielis Blog - Sat, 05/02/2020 - 08:18
One of the new features in Oracle Application Express (APEX) 20.1 is the integration with APEX Office Print (AOP).

In this blog post, I want to cover what this integration means and how you can set up and use AOP in APEX 20.1.

Background printing/exporting in Oracle APEX

Oracle APEX 3.0 introduced the ability to export a report region to PDF - essentially, printing a report. This is defined declaratively and is available for all report regions. You can view and print reports with page headings and footers that repeat on each page and content that properly conforms to various page sizes. To accomplish this, report data is transformed into PDF format using an externally defined report server. In the early days, this external report server was Oracle BI Publisher or an External Apache FOP server. Later, when ORDS came out, an FOP server was included as an option.
I believe that the External Apache FOP wasn't used much, as it was too complex to set up and use. BI Publisher was used by people who were able to pay for the (huge) license or who already had it in their company. In my experience, ORDS was the most used Print Server. But, in the ORDS 18.4 release notes, it was announced that the built-in FOP server would be deprecated and with ORDS 19.4, FOP was taken out completely.
This meant people needed to stay on an earlier version of ORDS or use something else...

About APEX Office Print (AOP)

APEX Office Print (AOP) has been around for more than 5 years and makes exporting and printing from Oracle APEX easy and affordable. Based on templates you create in Word, Excel, Powerpoint, HTML, and Text, AOP merges data coming from APEX regions, data from the database, or even external data with templates in the defined output format for example PDF or any of the template formats. In the last years, AOP has gained a lot of popularity and has become the go-to printing engine for Oracle APEX projects.

Last year Oracle announced they would build an integration with APEX Office Print directly in APEX and with APEX 20.1 this has now been made available. In this first iteration, AOP serves as a replacement for the FOP server, in future Oracle APEX versions, more AOP integration is planned.

Configuring AOP in Oracle APEX 20.1

To configure AOP as your Print Server, log in to the INTERNAL workspace and go to Instance Settings:

Click on Report Printing and select APEX Office Print as the Print Server:

Fill in the host address where the AOP Server is running. AOP can run in the AOP Cloud as configured above, or the AOP Server can run locally (on-premises). You can get a FREE Cloud subscription on apexofficeprint.com or you can download the on-premises version.

That's it! In APEX 20.1 you configure the Print Server on the instance level. In APEX 20.2 it's planned for you to be able to override these settings on the workspace level, so in case you have different AOP Servers or Cloud accounts for different projects, you can define them there.

Using AOP in Oracle APEX 20.1

To see the native integration of AOP in action, you can go to any Classic Report or Interactive Report and hit the Print or Download to PDF link.

The Print link in a Classic Report:

The output will look something like this:

You can customize the output in the Print Attributes of the Region:

You have the same feature when clicking Actions > Download > PDF in an Interactive Report

This is the basic integration of AOP in Oracle APEX, which mimics what ORDS, BI Publisher or an External FOP Server provided.

APEX Office Print can actually do much more than the above. AOP can print one or more Interactive Reports exactly as you see it on the screen, including highlights, breaks, filters etc. It can also combine IRs with IGs and Classic Reports and even charts all in a custom template you provide.

If you want this functionality, you still need to use the AOP Plug-in or AOP PL/SQL API, just like you did before APEX 20.1. You can download that from the APEX Office Print website.

I strongly advise you to also install the AOP Sample App, as it showcases all the features AOP gained in the last 5 years. On every page, you can see how the example is being built and which template has been used.

What does the future bring?

As with any future plans, things are not yet set in stone, so safe harbor applies here... but the plans are that APEX Office Print (AOP) will be further integrated with Oracle Application Express (APEX).
In APEX 20.1 you can download to PDF, but as AOP can also export to Word and Excel, it makes sense to give you this functionality too. Further on AOP comes with many other features, we will see how we can make those available to you in an even easier way :)

In another blog post, I will talk about the difference between native PDF printing which was introduced in APEX 20.1 and the AOP integration.

Happy exporting and printing!

Fix ORDS issue: No pools configured yet

Dimitri Gielis Blog - Sun, 04/26/2020 - 07:14
In a previous post, I blogged how I do my Oracle APEX upgrade, and part of that is to copy ORDS.

But when I tried to start ORDS, I got the "No pools configured yet" in my logs. 

ORDS was running, but APEX wasn't accessible.
I spend my night looking at this, as I already had done so many ORDS installation and configurations, but still was puzzled with this... so thought to share the fix, even if it's just so I can remember the next time it happens ;)
Background:- My Oracle database is a CDB with multiple PDBs- For every PDB I have a separate ORDS running in standalone
When doing an upgrade of APEX I do:- Copy PDB called APEX_PDB to APEX[version]_PDB (e.g. APEX192_PDB)
For ORDS I did this:- Copy directory ords to ords192- In ords192, change ords.war to ords192.war, set the new path for the configdir and in the standalone directory change standalone.properties to serve a different port and path:

Doing the above, ORDS was running nicely but gave me the "No pools configured yet" error, which also means APEX wasn't accessible.
If in my standalone.properties I wouldn't change the path to /ords192 but keep /ords it worked just fine. But I didn't want that, as I'm used to use /ords[apex_version]
So what finally worked for me, was the following:- mv the ords directory to ords_old- create a new directory ords and unpack the ORDS zip- rename ords.war to ords192.war- set the config dir of ords192.war- run java -jar ords.war setup and follow the wizard- run java -jar ords.war standalone and follow the wizard- quit- open the standalone.properties file and change the standalone.context.path=/ords to /ords192- start ORDS again
To me, it seems a combination of renaming the war file first, setting config dir and make sure to change the standalone properties as the default in there is /ords

Oracle APEX 20.1 installed with only 2 mins downtime

Dimitri Gielis Blog - Sun, 04/26/2020 - 06:33
Over the weekend I upgraded our environment to Oracle APEX 20.1.

I describe the process of installing a new version in the Safely Upgrading to Oracle APEX 18.1 blogpost. It comes down to cloning my current PDB, calling the clone the previous version of APEX, and then upgrade the current PDB to the latest APEX version.

This time I decided to not just run @apexins, instead, I wanted to try and minimize the downtime by following the steps described in the documentation under D Maximizing Uptime During an Application Express Upgrade.

Here're my notes, in case you want to maximize uptime.

During the first two phases, the Oracle APEX apps were still running:

SQL> @apexins1.sql sysaux sysaux temp /i/

SQL> @apexins2.sql sysaux sysaux temp /i/

The first two phases took about 7 minutes.
After phase 2, I stopped ORDS, and the downtime started for people wanting to use the Oracle APEX apps.

SQL> @apexins3.sql sysaux sysaux temp /i/

It completed in less than 2 minutes!

At the same time, phase 3 was running, I copied the images folder.
Note: you can also use the CDN by running @reset_image_prefix.sql after phase 3 and specifying: https://static.oracle.com/cdn/apex/

I ran the following command to allow APEX apps accessing, for example, AOP or other web services:

        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_200100',
                           principal_type => xs_acl.ptype_db));

And finally, I validated the ORDS config.

$ java -jar ords.war validate

I started ORDS again (running standalone), and the Oracle APEX apps were back online.

Very slick to do an upgrade with about 2 minutes downtime!

APEX Media Extension (AME) Publicly Available

Dimitri Gielis Blog - Mon, 03/02/2020 - 16:43
After two months of beta testing, we have released the first production version of APEX Media Extension v20.1.

I'm extremely proud to bring our second product to the Oracle and APEX community.  With our first product, APEX Office Print (AOP), we wanted to make the Printing and Exporting of documents easy.  With APEX Media Extension (AME) we want to make the processing of media easy for any Oracle database and Oracle APEX developer!

We got the idea of doing this product when Oracle Intermedia/Multimedia became decommissioned in the Oracle database, but we still needed the manipulation of images in our projects. We did research on the best use of existing tools, but it always seemed we couldn't rely on any one tool and we needed different tools and custom code to come to a solution.
In our first release of AME, you will find the following features:
  • resize images
  • rotate and flip images
  • crop images
  • add one or more watermarks or overlays (images and text) to images
  • compress images
  • greyscale images
  • change image formats (jpg, png, ...)
  • get image information (width, height, mime type, ...)
  • read media metadata (Exif, IPTC, and XMP)
Just like with AOP, AME also comes with a FREE plan.

You can get started by downloading the software from the APEX Media Extension (AME) website.
The zip contains a PL/SQL API, an Oracle APEX Plug-in, and an APEX sample application.

Here's a short video which shows you AME in action:

The architecture of AME is very similar to the one of AOP. There's an AME Server that accepts REST requests. On top of the REST API, we've built a user-friendly PL/SQL API.
The APEX Plug-in is a bit different, as this plug-in is an enhancement of the Dropzone plug-in by Daniel Hochleitner. We customized the plug-in so the manipulation of the images is done in the browser before the media is sent to the server.

As we love our AOP customers, we decided to put AME in our upcoming AOP release! So when you upgrade to AOP v20.1 (Gold and Enterprise on-premises licenses), you can use AME too without needing to do a separate install :)

Just like with AOP, we will have a separate AME Roadmap and of course, your voice is important to us! So let us know what you would like to see.

Here's already a quick preview of what we currently have in the pipeline:
  • audio manipulation and metadata info
  • video manipulation and metadata info
  • online photo editor
So what's next?

I will do six blog posts with different use cases of AME, based on real-world examples, so you can see AME in action.

1. In our project for a flight school, we had to automatically create thumbnails and resize images.

2. An auction company wanted to allow people to upload pictures of the goods they want to sell, but every picture needs a watermark and stored on a specific location.

3. The Belgium Tennis Organization wants uniformity in images, so they store the original image and bring them to the same mime type, resolution and compression rate.

4. A photographer wanted to do analysis on his pictures; where it was taken (GPS location), if they used flash, which aperture it was set to etc. This meta data is stored with tags encoded using Exif, IPTC, and XMP.

5. Cavanti is allowing veterinarians to draw the issues a horse has directly on an image. So the final picture is overlain with different images.

6. A hotel in Belgium wanted to grayscale and rotate images

These posts will showcase the features of AME and the power it can bring to your projects. Try out APEX Media Extension (AME), we hope you enjoy it and feel free to let us know what you are searching for!

Free Oracle Cloud: 19. Oracle APEX upgrades (19.1 to 19.2)

Dimitri Gielis Blog - Sun, 02/16/2020 - 20:16
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

Many people wonder how you can upgrade your Oracle APEX version to the latest version in your cloud account. The short answer is: you can't...

Oracle is doing this for you, whenever they believe it is the right time to do. The idea is that at some point you can tell Oracle when it would be a good time to upgrade your APEX environment in a specified window, but today, that is not there yet.

I read that a few days ago Oracle upgraded the APEX release to 19.2 on the free cloud.

I didn't use my Always FREE Oracle Cloud account for a few weeks, so it was stopped.
I went in the Oracle Cloud console to start it again and up we go.

Through the Tools menu, I open APEX and logged in into my CLOUD workspace. But, to my surprise my APEX version was still 19.1.

I was doing a few things when suddenly I got "Application Express is being upgraded to a newer version":

When trying to open APEX from the console, I couldn't anymore.

I waited a couple of minutes and there it was ... APEX came back and was upgraded to 19.2:

So it looks like Oracle is checking your APEX release when you start your database and when it sees it's an older version, it will just upgrade. I thought that probably because my account was stopped, Oracle will try again in the maintenance window, but my experience learns it's not waiting on that window.

I'm glad Oracle upgraded APEX for me automatically, but it would be nice to have a log somewhere or some info that it will do this. Hopefully, this blog post will help people who only fire up their instance occasionally when doing a demo. It's best to do it a bit before so the upgrade takes place before you need your APEX instance.

Free Oracle Cloud: 7. Setup a web server on the Virtual Machine

Dimitri Gielis Blog - Sat, 02/08/2020 - 20:37
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

In this blog post we will configure a web server on our Compute VM Instance. This allows us to host some websites and have a custom URL for our Oracle APEX instance and applications.

Lets start with connecting to our VM:

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 a web server 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

Although it doesn't really have anything to do with setting up a web server, I do want to share this... The first thing I always like to do on a machine, is get the system updated, so all latest software is being used. To do this, run following command:

yum update

It will take some time the first time, but after a couple of minutes you should see that all packages were updated:

So the purpose of this post was to install a web server so when we type in a certain domain, it will arrive at our machine. As web server, I typically chose between Apache and Nginx. Which one to choose is a hard one... if you search Google for "Apache vs Nginx" you can start reading ;) Since last year I started to use Nginx for all my new systems, before I always used Apache.

Following steps show how you install the Nginx web server and run it:

yum install nginx

Now we need to start the web server:

systemctl start nginx

To see if Nginx is successfully running, do:

systemctl status nginx

You should see something like:

The next thing we have to do is open the firewall on the Linux box, so incoming connections are allowed. The first line will open HTTP, the second HTTPS and then we reload the firewall:

firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload

Opening the firewall on the Linux box is not enough. Oracle added some extra security layers around the VM (Compute Instance). We have to allow HTTP and HTTPS access to our machine in the Oracle Firewall too.

Click the Virtual Cloud Network link:

Click the Security Links:

And add two Ingress Rules, one for HTTP and one for HTTPS:

As Source add so everything can connect to it and as destination port you specify the first time 80 (for HTTP) and the second time 443 (for HTTPS):

Once both Ingress Rules are added, your list looks like this:

Now you can navigate in a browser to your Public IP and you should see:

Now that we have a web server running and it's accessible through the IP address, we know things are working. Most of the time however you don't want to access your server through an IP address, rather you want people to use a domain name. To access my Free Oracle Cloud server for example I want to use the dgielis.com domain.

The first step to do, is in the domain provider you specify for the A record, the IP address of your Oracle Cloud VM (Compute) Instance.  I typically also setup some CNAME so any sub-domain will work too. For example I could point apex.dgielis.com to Oracle APEX Builder.

Now that the domain points to our VM, we have to make sure our web server listens to this domain and knows what to do. We will need to configure Nginx for this dgielis.com domain.

Here are the steps to do this (do this in your Terminal which is connected to your VM):

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

# Add following to the file (change dgielis.com by your domain):
server {
    listen         80;
    listen         [::]:80;
    server_name    dgielis.com www.dgielis.com;
    root           /usr/share/nginx/html/dgielis.com;
    index          index.html;
    try_files $uri /index.html;

# Create a directory where your website resides:
mkdir /usr/share/nginx/html/dgielis.com

# Add an index.html file to the directory
# Quickest way is vi or cp an index.html in this folder
# Or develop your site locally first and when ready upload with scp
# scp -i .ssh/oraclecloud /Users/dgielis/site.zip opc@

# to test Nginx configuration
nginx -t 

# to restart Nginx
nginx -s reload

# note: in case nginx doesn't restart, kill the nginx process and try to restart again
ps -ef | grep nginx
kill ~pid~

When you go in your browser to your domain name, it should show your website!

This website runs over HTTP, but these days it's recommended to use HTTPS for your sites. Lets setup HTTPS for our website by using LetsEncrypt, a free service for SSL certificates.

First we have to install some supporting packages:

yum install certbot python2-certbot-nginx  # not necessary
yum install python27-python-pip
scl enable python27 bash
pip install certbot
pip install setuptools --upgrade
pip install certbot-nginx

Once the supporting packages are there, we can run certbot to setup the SSL certificates for our domain:

certbot --nginx

After completion of the wizard, you should see something like below:

During the Certbot wizard which configures LetsEncrypt, it asks if you want to redirect all HTTP access to HTTPS and I would answer Yes here.

Now, regardless if you use HTTP or HTTPS on dgielis.com, you will always end-up with HTTPS.
HTTPS is better for Google, better for security, so no reason not to do it :)

If we want to use our Nginx web server as reverse proxy for our APEX environment we can do that by adapting our /etc/nginx/conf.d/dgielis.com.conf file (see the location sections):

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

Add following to the server:

  location /ords/ {
    proxy_pass your_apex_url/ords/;
    proxy_set_header Origin "" ;
    proxy_set_header X-Forwarded-Host $host:$server_port;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_connect_timeout       600;
    proxy_send_timeout          600;
    proxy_read_timeout          600;
    send_timeout                600;

  location /i/ {
    proxy_pass your_apex_url/i/;
    proxy_set_header X-Forwarded-Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

The final configuration file looks like this:

There's one other configuration change I would suggest you do straightaway; increase the size of the max_body_size in Nginx. Add following line to nginx.conf

vi /etc/nginx/nginx.conf

Test and reload the configuration of Nginx:

nginx -t
nginx -s reload

When going in a browser to https://dgielis.com/ords/ we arrive at Oracle APEX:

There's a lot of optimization we can do on the reverse proxy. To gain performance we can put the images folder of APEX on the Nginx server, as Nginx is super fast in transferring static files. We can add more redirects, for example, that apex.dgielis.com goes to the APEX builder and app.dgielis.com goes to one of our custom APEX apps. As this post is already long, I'm not including that in here.

Once Oracle provides vanity URLs, we don't need to do the above, and the URL will point directly to ATP.

Update 26-SEP-2019 (thanks Kris Rice): Note that setting the Origin would negate any CORS info that ORDS is enforcing. That could be a security issue for some people. Oracle is looking into the ability to have your ORDS running on your own Compute VM (the webserver we just setup), which would solve the issue. The vanity URLs would not have the CORS issue either.

In the next post, we will use this server to add an on-premises version of APEX Office Print (AOP), so we have everything we need to export data from the database in the format we want, for example in Excel and PDF.

Update 23-DEC-2019: If you are looking for other redirects see 17. Configure domain to redirect to APEX app

Print PDF from Oracle APEX to Local or Remote Printer

Dimitri Gielis Blog - Sun, 01/19/2020 - 19:33
On Twitter, I got a lot of reactions on my previous blog post Printing to local (Zebra) printer from Oracle APEX in the Cloud.

As more people asked the question of how to print to a local printer from Oracle APEX, I will cover 3 options in this post. All options make use of some features of APEX Office Print (AOP).

When you install the AOP Sample Application, you find the examples under the following sections:

1. Call Print Window of the browser directly from a button

Create a button on the page e.g. PDF and add a Dynamic Action to it. The Dynamic Action calls some JavaScript:

This JavaScript calls following AJAX Process, which uses the AOP PL/SQL API to generate a PDF:

When the PDF is produced, the printJS JavaScript function is being called, which opens the browser's print window. The end-user can change the Printer and hit the Print button.
So this option is not a direct print to a predefined printer, but a two-step process: open print window, hit print.

2. Print directly to an IP Printer

One of APEX Office Print's unique features is that the AOP Server can send a document directly to the IP address of a printer. AOP is really smart and if it sees the printer doesn't support native PDF or the document type we send, it will convert to PDF or Postscript, which all printers support.

The only thing you have to do, in order to print directly to an IP Printer is to add some global variables of AOP. In the below example it will send a PDF to the IP Printer on the same server AOP is running but on port 3000.

3. Use of Post-Process command

You can configure the AOP Server with some post-processing commands, which allow you to run some command-line tools before and after the AOP Server is creating the output. This is a really powerful feature and one use case is to send the document directly to a local (or network or remote) printer.

Just like with the IP Printer, you define the post-process command in some global variables of AOP.

This technique I also described in the previous blog post where we printed to a local Zebra printer. The only special thing in that post was that we used ngroc to make AOP known to the Oracle Cloud.
When you have the AOP Server running on-premises, you don't have that step as your APEX app will call the on-premises AOP Server. The only thing you need to make sure is that AOP Server knows the printer you configured in the post-process command.

There you go, three options to print from your Oracle APEX app directly to a local or remote printer using APEX Office Print.

Merry Christmas and Happy New Year !! (“Heroes”)

Richard Foote's Blog - Mon, 12/23/2019 - 22:01
I would like to take this opportunity to wish all my readers a very Merry Christmas and a most happy, peaceful and prosperous New Year. My gift this year is not David Bowie and Bing Crosby doing their famous Christmas duet but a performance by Bowie of his classic “Heroes” as featured on the same […]

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars

Richard Foote's Blog - Thu, 12/19/2019 - 08:06
Places are filling up, but there are still some available at both of my acclaimed seminars that I’ll be running in London, UK in March 2020. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]

Oracle Database 19c Automatic Indexing – Indexed Column Reorder (What Shall We Do Now?)

Richard Foote's Blog - Wed, 12/18/2019 - 00:49
  I previously discussed how the default column order of an Automatic Index (in the absence of other factors) is based on the Column ID, the order in which the columns are defined in the table. But what if there are “other factors” based on new workloads and the original index column order is no […]

Oracle Database 19c Automatic Indexing: Index Compression (Ghosteen)

Richard Foote's Blog - Mon, 12/09/2019 - 03:01
    In my previous post on Automatic Indexing, I discussed how the default index column order (in absence of other factors) is column id, the order in which the columns are defined in the table. In this post, I’ll explore if this changes if index compression is also implemented. By default, Automatic Indexing does […]

Presenting at UKOUG Techfest19 Conference in Brighton, UK

Richard Foote's Blog - Fri, 09/13/2019 - 00:07
I’m very excited to be attending my 3rd UKOUG Conference, this year re-badged as Techfest19. The fact it’s being held in Brighton is a little disconcerting for a Crystal Palace fan, but really looking forward nonetheless to what has always been one of the very best Oracle conferences on the yearly calendar. I have a […]
Subscribe to Better Logic LLC aggregator