Monday, January 18, 2021

Setup and configure the APEX Application Development Service (including email) in the Oracle Cloud

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

In this post, we will set up everything you need to build an Oracle APEX application that is able to also send emails.

I expect you already have an Oracle Cloud account. If not, it's easy to sign up for one by following the wizards. This post starts when you enter the Oracle Cloud Getting Started page.


Click the menu icon on the top left, select APEX Application Development, and click on APEX Instances:

Click the Create APEX Service button and fill out the details.

The database name can only be 14 characters and at the moment you can only choose a 19c database.
Behind the APEX Service is an Autonomous Oracle Database, optimized for APEX. 

I love this APEX Service (and in general the Autonomous Oracle Database) because it can auto-scale. We select 1 OCPU and 1TB of storage, but with Autoscaling it can go up to 3 OCPUs depending on the load. This is a major feature as in almost all the projects I'm involved in, you need, from time to time, just a little extra power (e.g. end-of-month calculations or heavy traffic to your app after an event). In my World Cup Football betting app, for example, every time a game was finished I had a huge spike in visitors as people wanted to know their scores and rank. And the cool thing is that it doesn't require downtime and happens automatically! Note that when it effectively auto-scales you pay for the extra OCPU and potential extra storage, but only for the time it auto-scaled.


Furthermore, define the password for the ADMIN user, which is needed to login to SQL Developer Web and Oracle APEX. And finally, define the network access. As I want the Oracle APEX apps accessible from anywhere in the world, I chose "Allow secure access from everywhere". Note that you can always change the access and define access control rules later.


Hit the Create APEX Service and the magic will start!


A few minutes later the APEX Instance is ready to be used. Behind the scenes, there's an Oracle Database, APEX (20.2 at the time of writing), and ORDS (20.3 at the time of writing) fully configured.


We can now access APEX by clicking on the "Launch APEX" button and create REST services and get into the Oracle database by clicking the "Launch SQL Developer Web" button.

But before we build an Oracle APEX app, we need to do one extra thing: configure the Oracle Cloud and APEX to allow to send emails. It's a two-step process: create SMTP credentials and enter the email addresses from which emails are allowed to be sent.

To get the SMTP credentials, go to Identity > Users and click on a user


In the details of the user, click the link SMTP Credentials


Click the Generate SMTP Credentials:


Enter a description and click the Generate SMTP Credentials button:


A username and password are created. Make sure to copy both:


Next, we will add the email addresses from which emails can be sent.

Go to Email Delivery and click on Email Approved Senders and hit the Create Approved Sender button.

Enter the Email Address and hit the Create Approved Sender button.


Repeat the same for other email addresses you want to send from.


With United Codes we own both domains: united-codes.com and unitedcodes.com, so I enabled both my email addresses. 


Click the Email Configuration and copy the Public Endpoint (smtp.email....):


Finally, we will configure the Oracle APEX instance with the SMTP credentials.

Head back to the APEX Instance Details


Click the Launch SQL Developer Web button and login with ADMIN and the password which was defined during the creation of the service


The first time you open SQL Developer Web, there's a nice guided tour that explains the functionality in SQL Developer Web. (Note: in case you are interested in a Guided Tour for your own Oracle APEX app, have a look at Plug-ins Pro, we've built a Guided Tour APEX Plug-in which makes it very easy to provide your users with a nice interactive guide.)


Finally, we arrive at a SQL Worksheet, in which you can type any SQL or PL/SQL statement:


By using the APEX_INSTANCE_ADMIN package we can define the SMTP configuration:

set define off

BEGIN
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp...');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'o...');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'k...');
  COMMIT;
END;
/

Click the green arrow with the paper, which runs the commands as a script:


That's it, now we are ready to send emails from our APEX instance.

Before we login into APEX, I looked a bit more around in SQL Developer Web. For example, I saw that the previous versions of Oracle APEX are still there:


Trying to delete the older versions or some objects gave me insufficient privileges. This is good, as it means you can't accidentally do something bad:


There are also some rules to follow, for example when I wanted to create a new Oracle database user, it has to fulfill some rules. It took me a bit to figure out which ones:


Anyway, I really like SQL Developer Web. It has many great features and gives you access to your database by just using a browser.

So now, let's get started with APEX.  From the APEX Instance Details click the Launch APEX button.

The first time we try to login into APEX, we need to provide the password we defined during the creation of the service. Hit Sign in to Administration:


We get a nice welcome message and it asks us to create a new Workspace. Hit the Create Workspace button:


Define a new database user, a password, and a workspace name and hit the Create Workspace button:


A few seconds later the workspace is created and we can log in to that Workspace to create our first Oracle APEX application. Click the workspace name link:


Enter the workspace name, the username (which is the database user), and the password and hit the Sign In button:


And here we are in Oracle APEX. Now we are ready to build APEX applications. 


To test if the sending of emails work, head over to SQL Workshop > SQL Commands and enter the following code:

begin
  apex_mail.send(
        p_to       => 'xxx',
        p_from     => 'xxx',
        p_body     => 'Testing email from Oracle APEX Cloud service',
        p_subj     => 'APEX_MAIL Package - Plain Text message');
  apex_mail.push_queue;    
end;

It should run fine:


Check APEX_MAIL_LOG to see if the email was effectively sent:


In case you don't see an entry, it probably means the email is hanging in the mail queue. Check the APEX_MAIL_QUEUE for possible errors:

The errors that I've come across are the following:

  • ORA-29278: SMTP transient error: 421 Service not available
  • ORA-29279: SMTP permanent error: 535 Authorization failed: Envelope From address <...> not authorized

The first error most likely means the SMTP credentials are not entered correctly (see SMTP credentials).
The second error most likely means that the email from the address is not allowed to send emails (see Email Delivery).

Before I finish this blog post I want to highlight that from the APEX Service, you can also see the details of the Autonomous Oracle Database that was created behind the scenes.

When you are in the APEX Instance Details, click on the link of the Database Name.


We will now get the details of the Autonomous Database


In case you want to stop the APEX instance or terminate it, you have to go to the database to do this. See the More Actions button. Also when you want to manually Scale Up or Down, you do it from this database screen and not the APEX instance screen. As some might search for it, I thought to point it out in this post. In my series where we set up an Always Free Oracle Cloud, we get a free Oracle Autonomous Database with APEX and SQL Developer Web too, so you might wonder, what is the difference with setting up an APEX instance? The components look identical, but I did notice one difference, the workload type is now called APEX instead of, for example, Transaction Processing:


But regardless of the workload type, you will see all the Autonomous Databases also in the APEX Instances screen:


When clicking on an APEX instance name, you see metrics of the APEX instance:


Now, you could also set up the APEX instance through the Autonomous Database wizard and select the APEX workload type:


The difference is that when you follow the APEX Application Development, settings are pre-set or hidden, so it's a more seamless experience. But I hope that in the future when you select to use this new wizard, the wizard will also guide you to set up the email delivery and SMTP credentials automatically, so all those steps are done for you. Object Storage would be another good candidate to set up automatically, for example, create a bucket with the same name as the APEX instance. This would also differentiate it more from using the Create Autonomous Database wizard.

That's it for now... next up, I will import an existing database (schema) and Oracle APEX app into the environment we just created.

Friday, January 15, 2021

My spin on the new Oracle APEX Application Development Service

I believe there have never been bigger announcements surrounding Oracle Application Express (APEX) than in the last weeks (or actually months). Oracle is really putting cannons on the marketing of Oracle APEX now!

In the Oracle APEX community, we typically come together at the many APEX conferences in the world. Things changed with COVID and while all the conferences got canceled, the awareness of Oracle APEX rose significantly. Oracle setup special forces to build COVID apps (including Larry Ellison himself!)... and used Oracle APEX to do so. Those apps were mentioned in the White House and many different articles around the world. Not only on the outside things changed, but also on the inside at Oracle, the view towards APEX changed a lot. Oracle APEX finally received the recognition of the leadership team we already knew it deserved for 15 years. And once Larry Ellison, Safra Catz, and the entire management and marketing team get behind something, it's unstoppable.  

It started on December 8th through the 10th, 2020, with the Build Low Code Apps using APEX - Partner Technical Council 2020, where Joel Kallman and many people of the APEX Development team gave an update and did Q&A with the audience. 

December 16th, 2020, with the Virtual Summit Build Applications Faster with Low Code. There were different presentations, but the biggest impact came from Michael Hichwa, the father of Oracle APEX, who announced the new Oracle APEX Application Development Service.

On January 13th, 2021, Andrew Mendelsohn, executive vice president of Oracle database server technologies, presented the latest announcements about Oracle Database 21c and the new Oracle Application Express (APEX) low-code application development service


This was followed by a press release the same day.

Today when you go to oracle.com, Oracle APEX is front and center.

 

So things changed a bit compared to a few years ago... so let's go back to why I am writing this blog post.

This new Oracle APEX Application Development Service is an interesting spin on the Oracle Cloud. Where before you would set up an Oracle Database and Oracle APEX came with it, with this service it's the other way around! You sign up for a Low Code Development Platform called Oracle APEX, and you get an Oracle Database with it!

I really like this service, because for a relatively low price (360 USD all costs included) you get a fully managed Oracle APEX instance running on unbeatable hardware (Exadata), which is able to autoscale based on your needs!

And what is even better, it looks like this service will also come in the Oracle Cloud Free Tier! I wonder if the disk space is the same as the ATP free tier... remains to be seen. At the moment you can either buy it or try it with the free credits when you sign up.

Just like my blog series when Oracle launched the Always Free Oracle Cloud, I thought to write a few posts while I set up and test this new service myself.

Here's my agenda for the upcoming blog posts (subject to change based on my experiences and questions raised by you):

  1. Setup Oracle APEX Application Development Service (include configure of email sending)
  2. Create a custom domain name
  3. Configure printing and exporting
  4. Configure media conversion
  5. Import an existing Oracle APEX app and Oracle database schema
  6. Setup and test automated scaling
  7. Configure, running and using backups
  8. Connecting from external tools e.g. Visual Studio Code and SQL Developer
  9. Monitor your Oracle APEX instance
  10. Final thoughts
I will update this post with the links to the new blog posts.

Sunday, November 22, 2020

My steps to upgrade to Oracle APEX 20.2, ORDS 20.3 and AOP 20.3

In the last month, the three major components of an Oracle APEX environment got new versions:
- on October 29, 2020, Oracle REST Data Services (ORDS) 20.3 
- on November 15, 2020, APEX Office Print (AOP) 20.3 

In general, the releases are as follows (based on findings from 2018 onwards):
- APEX has two releases a year, around March/April and one around September/October.
- ORDS brings out one release per quarter, but from time to time they might skip a quarter.
- AOP has three major releases per year and some smaller releases in between.

Do we upgrade our production environment with every new release? 
- APEX: yes, we upgrade typically once the patchset bundle is available, so every 6 months
- ORDS: no, we upgrade ORDS once a year, unless we need a specific feature
- AOP: yes, at least the major releases are followed for the on-premises AOP release. AOP Cloud always has the latest release by default.

When I look at our customers, many don't upgrade that fast. The bigger the corporation the slower the uptake, going from every 4 years to yearly upgrades.

This weekend we upgraded an environment to bring everything to the latest release. Here's a breakdown of my tasks and some tips.

1. Download all the latest software

- Go to the website Oracle Application Express (APEX) 20.2, click the download link, once you log in the download starts.  If you want to be on the latest patch set, then also click the link to download Patch Set Bundle for Oracle APEX 20.2  (32006852). You will need to have a valid support contract with Oracle in order to download the patchset bundle.

- Go to the website Oracle REST Data Services (ORDS) 20.3, click the download link, once you log in the download starts. 

- Go to the website APEX Office Print (AOP) 20.3, login (or signup if you don't have an account yet), go to the downloads section, and download the on-premise version of Linux or Windows.


2. Prepare installs

- Copy all the zip files from step 1 to the server(s). In our case, all of the above software is running on the same server, but many people have separate servers for the DB (where APEX is), ORDS, and AOP.

- Prepare the directories and unzip

- I typically export all Workspaces and APEX apps before doing the upgrade, in the case of APEX 20.1, and store them somewhere. This way I always have a copy of the app in that APEX version. Here's the command to do so:

/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -expWorkspace > workspaces.txt

/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -instance > applications.txt


3. Install Oracle APEX

Just as with the upgrade to Oracle APEX 20.1 I blogged about previously, I followed the same steps to maximize uptime during the APEX upgrade.

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 as specified in the doc, but which is not necessary anymore as ORDS is smart and knows APEX is upgrading and automatically suspends activity by itself. 
(As I wanted to upgrade ORDS too I stopped it - read more in ORDS install about that)

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


It completed in about 3.30 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/20.2.0.00.20/

With the above steps, the APEX 20.2 install completed. Now, I applied the patchset bundle, which took about 1 minute.

SQL> @catpatch


Finally, I ran the following command to allow APEX apps to access web services:

SQL> BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_200200',
                           principal_type => xs_acl.ptype_db));
END;

That was pretty much it to upgrade our Oracle APEX 20.1 to 20.2 release.


4. Install ORDS

Every pluggable database and APEX version has its own ORDS. Whenever I upgrade ORDS, I unzip it in a new directory. I copy the config of the previous ORDS version (the whole ords directory) in the new directory where I unzipped ORDS. In my case, I unzipped ORDS 20.3 in /u01/apex202/ords.
Next to the config, I also copy the start and stop scripts and create a logs directory.


Next, I run the ORDS command to set the config directory in the new ords.war file.

java -jar ords.war configdir /u01/apex202/ords

To upgrade ORDS I run: 

java -jar ords.war 

This will connect to the database, identify the meta-data, upgrade it, and run ORDS.
Once it's complete, I stop ORDS and run it with the start_ords.sh script, so proper logging is done. 
Note, in this environment we run ORDS in standalone mode.

Now, with the install of ORDS, I made a mistake... I thought to be smart, and upgrade ORDS at the same time when APEX was doing the final install step (see in APEX, when I stopped ORDS).
This wasn't so smart to do! It looks like ORDS is looking at the APEX version to generate some repository views. It messed up things, as ORDS saw still APEX 20.1 as the flip of APEX versions was still going on. Anyway, I got an error. So I decided to wait until the APEX upgrade was finished, then I ran the ORDS command again and it completed fine.  All things were validated ok, start ORDS and we were up-and-running in APEX 20.2 and ORDS 20.3.  Tested the apps, all ok... but for one domain, after a few minutes, I saw an error in the ORDS logs:

2020-11-21T11:19:38.182Z INFO        <CaNhATfOdgVPIOr6aSAtzw> GET www.xxxx.com /ords/f?p=XXXX:LOGIN:0 403
ProcedureForbiddenException [statusCode=403, logLevel=INFO, reasons=[Access to the procedure named: f is denied. ]]

I still can't explain why it happened. I solved it by running ORDS validate again and restarting ORDS.

java -jar ords.war validate

My guess is, that the connection pool was messed up by running ORDS while APEX was not finished yet installing. So my recommendation is to always install APEX first and only once that is complete upgrade ORDS (if you want to do it at the same moment), or the other way around, install ORDS first and once complete upgrade APEX.


5. Install AOP

Upgrading AOP is the most simple of the three components, as it doesn't have a repository. Unzip the download in a new directory, copy the config (aop_config.json) of the running AOP to the new folder and activate AOP:

./APEXOfficePrintLinux64 -a

Stop the old version of AOP, start the new version of AOP, and done.


Happy upgrading!

Saturday, November 14, 2020

Oracle APEX 20.2: APEX Office Print (AOP) vs native PDF, Excel and HTML

One of the most loved features of APEX Office Print (AOP) is the ability to export an Interactive Report or Grid exactly as you see it on the screen to Excel and PDF. AOP takes into account highlighting, filters, and more as defined by the end-user. This feature has been available since AOP 1.5 (2015) on APEX 5.0 and any higher version of APEX.

With the release of Oracle APEX 20.2, APEX provides this capability out-of-the-box. Because of this, we have received a few questions like: "Is this AOP functionality?", "How does the native APEX functionality compare with AOP?", "What's the roadmap of AOP?" and I thought the best way to answer would be to write about the differences between APEX Office Print and the native printing functionality in APEX 20.2.

Let's go back to the start... AOP's mission has always been to make the printing and exporting of data in Oracle APEX in the format you want  as easy as possible. We really believe that business users should be able to create a template in a tool they know (Word, Excel, Powerpoint) and let AOP merge it with data from the database. From day 1, APEX Office Print has been a template-based reporting engine. In your template, you use tags, which AOP replaces with data. When no custom template is selected AOP will use a default AOP Template. AOP is the most integrated printing solution for Oracle APEX out there and provides some unique features like the ability to print and export different regions by providing a single tag. For example, to print an Interactive Report as you see it on the screen you put {&interactive} in your template, to print a chart you use {$chart}, an image you use {%image}, etc.

The built-in exporting solution of Oracle APEX started from a different angle. The APEX development team wanted a PL/SQL only solution to allow people to export data/regions in different formats. Oracle APEX 20.2 is a big advancement to what was already available previously. For example, before you could download a report to CSV, but now APEX allows you to download to native Excel. Also, for Interactive Reports and Grids, you can choose to get the export as you have it on the screen with breaks, etc. In addition to the built-in download of a region, they now expose APIs (APEX_REGION and APEX_DATA_EXPORT packages) to export programmatically. 

Let's look at the questions people ask: 

Is this AOP functionality?

No, it's not, in the sense that the creation of the file happens completely in PL/SQL in native APEX, whereas AOP reads the APEX meta-data, creates a JSON, and sends it to the AOP Server which creates the file and serves it back to the database. Of course, reading the meta-data is the same and the output for a single report is very similar when you don't define a custom template in AOP. 

Although AOP is super fast in creating files (< 0.5 seconds!), the native APEX functionality will be the fastest solution to export data as it stays within the database. This is why from AOP 20.3 onwards we also now provide the option to select APEX Report, which will use the native APEX functionality behind the scenes.

How does the native APEX functionality compare with AOP? 

The native functionality in Oracle APEX 20.2 focuses on exporting a single region or some flat data (single select statement). This is vastly different from AOP, which focuses on printing and exporting as a whole. AOP is a full templating and printing engine with hundreds of features to create pixel-perfect PDFs and export the data out of your database in the format you want. If you need to create custom letters, bills of lading, documents with images and charts, perform some PDF manipulations like merging, splitting, signing or want to print directly to a printer, for example, AOP is what you need to use. 

When we don't look at general exporting and printing but focus on exporting of APEX regions and data, the native APEX functionality does a great job on basic functionalities. If you want more flexibility and advanced features, AOP is what you want to use. Here are a few examples where AOP shines:

  • put your logo on top of the export (PDF/Excel/HTML)
  • export multiple reports with some custom titles and text to PDF
  • export different Interactive Report, Interactive Grid, Classic Reports, and some custom data to different sheets in Excel
  • keep the styling (e.g. defined in HTML expressions) when exporting to PDF and Excel 
  • have complete freedom as to how the export looks and define your own template
  • support for many different languages
  • export the charts of Interactive Reports and Grids
  • reference APEX Items in your export
  • use a percent graph in your reports
  • export a specific saved private or public report
  • conditionally hide a specific column when you export
  • export Master-Detail(-Detail) Interactive Grids
  • export your reports to Word, Open Document Format, or Markdown
  • export hierarchical data and different blocks of data  

I think its great people can create some custom exports and prints with built-in tools and use AOP when more customization, flexibility, and features are needed. In fact, the new API functionality that is exposed in APEX 20.2 will make AOP even better, but more about that in my answer to the next question.

What's the roadmap of AOP?

We shipped the first version of AOP in March 2015 and since that day we have constantly invested in the product. We typically have 3 major releases per year and some smaller releases between them.

In fact, every year we have invested more in the product than the year before. 2020 is not any different. On the AOP 20.3 release, we have worked for 6 months with 8 people! It was one of the biggest and most challenging releases we have ever done, but it has made it our best release ever! A huge shout-out to the entire team who pulled it together: Sunil, Recep, Gibresh, Niyam, Kelvin, Inias, Jackie, and ... myself 😀

Two years ago it was announced that AOP will be more integrated into Oracle APEX. In APEX 20.1 and above, you can define APEX Office Print as your Print Server on the Instance Level, which replaced the functionality that was deprecated in ORDS. In APEX 20.2 the foundation of further integration of AOP is made available by providing the awesome APEX_REGION and APEX_DATA_EXPORT packages. 


If you've ever wondered why there's a 'PJSON' format in the APEX_DATA_EXPORT package... it's because we worked with the APEX Development team on this. In the last years, we've spent a lot of time understanding the meta-data of APEX and for every new component and release, we've had to put a lot of effort into supporting and testing it. The new packages provide us with a built-in way to get the region meta-data, so we are set up to support any future component.

The Oracle Database and APEX are in our hearts and we do everything we can to provide you with the best printing and exporting solution out there. Just like the APEX Development team, we are dedicated to making you successful. We also understand you might be using additional technologies to Oracle APEX and PL/SQL, and although, today, AOP can already be used from any technology by doing a REST call, we will begin providing SDKs for other technologies too.

I hope the above addresses some of your questions and I'm happy to answer any other you might ask in the comments section below or by emailing our AOP Support team.

Happy printing and exporting! 😀