Feed aggregator

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.

  I’ve been waiting a while before posting a series on the various limitations associated with Automatic Indexing, in order to see how the feature matures over time. The following have all been re-tested post 1 January 2021 on the Autonomous ATP Database Cloud service, using Oracle Database version 19.5.0.0.0. In the Oracle Documentation (including […]

In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions […]

  Firstly, thank you for all those who contacted me regarding the issue with Oracle Automatic Indexing (AI) having stopped working within the Autonomous ATP Database Cloud service. It appears this issue was indeed widely spread and impacted numerous (if not all) Autonomous ATP Database Cloud services. This was all possibly due to (unpublished) bug […]

  I’ve been playing with the free tier Oracle Autonomous Databases for quite some time, but unfortunately in recent times, I’ve hit a bit of a snag. The Automatic Index capability appears to be currently broken… The Automatic Indexing task appears to have stopped running, yes even with the AUTO_INDEX_MODE set to “IMPLEMENT” and with […]

  After much badgering from a number of you (you know who you all are), I’m pleased to finally announce the scheduling of 2 new webinars for February 2021 !! As usual, places are very strictly limited as I only run small classes to give every attendee the opportunity to get the most from the […]

In the last month, the three major components of an Oracle APEX environment got new versions:- on October 21, 2020, Oracle Application Express (APEX) 20.2 - 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 403ProcedureForbiddenException [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!

In this little series, I’m going to discuss how Automatic Indexing works in relation to Partitioning. I’ve discussed Indexing and Partitioning many times previously and how Oracle has various options when indexing a partitioned table: Non-Partitioned Index Globally Partitioned Index Locally Partitioned Index So the question(s) are how does Automatic Indexing handle scenarios with partitioned […]

In Part I of this series, we saw how Automatic Indexing will not create a viable Automatic Index if there are stale or missing statistics on the underlining segments. In Part II we saw how these SQL statements effectively become blacklisted and when segment statistics are subsequently collected, Automatic Indexing will still not create viable […]

    In my previous post, I discussed how having stale statistics, usually a bad idea, is especially problematic with regard Automatic Indexes as it usually results in viable automatic indexes only being created in an UNUSABLE/INVISIBLE state. If we were to now to collect the missing statistics:   If we now repeatedly re-run the […]

A “golden rule” when working with Automatic Indexing is that things don’t work properly if there are stale statistics on the dependant objects. Stale statistics can of course be problematic but they can be particularly troublesome when dealing with Automatic Indexing. In the Oracle Autonomous Database environments, this issue is addressed somewhat by the new […]

This post is part of the Getting Started with Plug-ins Pro APEX plug-ins series.

In many Oracle Application Express projects, there's a requirement that people can upload files. Typically the end-users want the ability to drag-and-drop images, PDFs, or other file types in their application. 

For example, if you use APEX Office Print, you might have added in your application the ability for end-users to upload their own templates which typically are docx, xlsx, pptx, html and text files. 

Those files are then stored in the database in a BLOB column or they might be stored on a file server or cloud storage.

In order to add this capability in your app, APEX provides a File Browse item.

There are a few settings for this item type; where do you want to store those files (in a BLOB column or in a TEMP table), if you want to allow multiple file uploads at once, and which types of file you want to accept.

But be careful with the accepted File Types feature. You can define image/* if you want your browser to only allow selecting of images. BUT specifying the file type doesn't prevent users from dragging-and-dropping other file types! So you can't rely on this feature to keep other file types out of your database. 

As this is a native HTML File Browse item, you can customize it with CSS (and HTML and JavaScript). For example, you can make it look like what APEX provides in the APEX Builder itself:

In most of the projects I'm involved in, the end-users want more than the standard HTML File Browse that Oracle APEX provides out-of-the-box. 

The most requested features are:

• Show which files were selected when adding multiple files
• A visual indicator of the progress of uploading
• Save files directly to the cloud (Oracle Cloud Object Storage, Amazon S3) instead of the database
• Limit the file types that will be accepted
• Only allow files with a size less than x MB
• Specific to upload of images: resize and compress images and add watermarks automatically
• Rename files

There are some excellent open-source JavaScript libraries that bring these features, one of the most known is Dropzone.js. Five years ago, Daniel Hochleitner even created an APEX Plug-in on top of it (with the last update 2 years ago). As this plug-in was really well written, we decided to use it as the starting point for our File & Image Uploader plug-in. You can see our plug-in as a supported version of Dropzone for Oracle APEX, with some extra features.

To use this plug-in, you first have to download and install it. I've covered that in my blog posts Plug-ins Pro: Getting started: Sign-up and install your first Oracle APEX Plug-in. So if you didn't import the File & Image Uploader Pro plug-in yet, read that post first.
I'll cover two use cases where we used our File & Image Uploader plug-in.
The first use case is for our RentMaster Platform software which assists in the renting of student homes. We have a form with the information of the contract, and a region which allows people to upload multiple files (up to 3, with a max file size of 5MB) that are linked to this contract. This information is stored in the CONTRACTS table and another CONTRACT_FILES table which has an FK to the CONTRACTS table. Here's a simplified version of the use case in action:

In order to create the above, we add a region with our plug-in on the left and a normal classic report on the right. We also have a Dynamic Action that fires when the files are uploaded so that the report on the right is refreshed.
The attributes of our plug-in region look like this. As storage type we use Custom Table:

The only thing we have to do is define the table in which we want to store the files and the relation (FK) with the parent (ID).
The plug-in exposes many dynamic action events, so you know exactly in which state the upload is in. We specified when Dropzone File Upload was successful:

Pretty simple and low code :)
In the second use case, our customer does auctions. With every auction, multiple images are included of the goods that are for sale. As there are so many images, all those images are resized and watermarked and put outside the database, on a file server and cloud storage. In this example, I will show how to do the image manipulation and save the files directly in the Object Storage of the Oracle Cloud.
Note: if you are interested in how to set up the Object Storage in the Oracle Cloud, read my series on the Always FREE Oracle Cloud.
In the next animated gif, you see again a simplified version in action. On the left the APEX application with the File & Image Uploader plug-in, on the right, the Oracle Cloud - Object Storage:

The attributes of our plug-in region look like this. As storage type we use this time Web Service:

Furthermore, in the attributes, we defined that we should only accept images with max size 3MB and only 10 files.
This time we also defined transformation parameters, a watermark and we rename the files on the fly.
In the United Codes File & Image Uploader Pro Sample Application, you will find an Attribute Builder, which makes it very easy to define what your transformations and watermark need to be, so you can just copy and paste in the attributes:

So we defined a Web Service... this web service can be anything. In my example, I created a web service in ORDS. In this web service, I make a call to the Object Storage REST API of the Oracle Cloud. The plug-in will pass some parameters to the web service: file, filename, mimetype, sessionid and appid:

When you look at the above, you might think... hmm, Dimitri is not doing any authentication to his Object Storage... that is correct. To simplify my example I setup Pre-Authenticated Requests for my bucket in the Object Storage. This provides me with a secure URL people can use to put files in my bucket.

I believe that most people will do proper authentication, which means you first do an extra call to get the authentication token. You may want to read JMJ Cloud's blog post if you want an example of how to do that from APEX. Adrian Png also did another blog post on how to deal with Object Storage from APEX.
Now back to the example... this is what the result looks like; the large image was scaled down, the quality was set to 80%, a watermark was added and the file was renamed - all on the fly and automatically!

So to recap this example: you add our region plug-in, you specify the actions you want to happen (transform, watermark, rename), define a web service which does the call to your cloud of choice and that's it, now whenever somebody uploads files in your Oracle APEX app, it will go straight to the cloud and all manipulations are done in the background!
There are many more use cases you can come up with. This plug-in is also part of our APEX Media Extension (AME). Using the Plug-ins Pro File & Image Uploader plug-in is great when you want things to happen on the client-side. When you already have images in your database, and you want to manipulate those images, you definitely want to check out AME, as it provides a PL/SQL API to do all that, and more (e.g. read meta-data of photos!). We built AME as a replacement for Oracle Intermedia (see blog post and here), we are even featured in the official Oracle documentation as a replacement.
Ok back, to this plug-in... just like any other Plug-ins Pro plug-in, there's extensive documentation and a great sample app that showcases the different features.
You can try the plug-in yourself for 1 month for free in your own APEX applications, so you can see what it can do for you. I also want to highlight that through the end of September 2020, there's a special running. You can find all the details on the Plug-ins Pro website!

  In my previous post, I discussed how the Automatic Indexing task by using Dynamic Sampling Level=11 can correctly determine the correct query cardinality estimates and assume the CBO will likewise determine the correct cardinality estimate and NOT use an index if it would cause performance to regress. However, if other database sessions DON’T use […]

In my previous few blog posts, I’ve been discussing some issues in relation to how Automatic Indexes handle SQL statements that accesses skewed data. In this post, I’m going to setup the scenario in which Automatic Indexing can potentially use Baselines to help address some of these issues. BUT, as we’ll see, I’m having to […]