Dimitri Gielis
![Subscribe to Dimitri Gielis feed](http://www.orafaq.com/misc/feed.png)
I created this Blog to share my knowledge especially in Oracle Application Express (APEX) and my feelings ...
Updated: 10 hours 23 min ago
Need to extend Oracle APEX? Welcome Plug-ins Pro!
After APEX Office Print (AOP), APEX Media Extension (AME), and APEX Project Eye (APE), we launched our newest product and service, Plug-ins Pro, in June.
Since 2004 I've used Oracle Application Express (APEX) and I just love it. With every new release of APEX things become easier and new features get added going from new item types like switches to rich components like charts. But the web is moving fast, and customers want more and more which means you need to go outside of what APEX provides. The main building blocks of APEX are the Oracle database, SQL, PL/SQL, HTML, CSS, and JavaScript. You can use any of those technologies to extend Oracle APEX with custom components. This is a real benefit of APEX, you can go from Low Code to High Code in no time. Really, there's no limit... only your imagination and skills.
Over time I've created many extensions for APEX, going from building reusable PL/SQL packages to integrating some JavaScript library. The issue with building those one-off customizations is that you have to maintain them. Where plain Oracle APEX applications stand the test of time very well, for example, an app built in 2006 still runs today, even after upgrading to the latest Oracle APEX version. Whenever I used custom code, especially JavaScript, it was less obvious to keep that current, and it would be one of the first things that break. Next to that, you make the code work specifically for a project, and in the next project you need something similar, so you copy the solution and make changes specific for that project. It's always in the context of a project that things are built, never looking at the bigger picture.
As the Oracle APEX team identified the need to extend APEX and wanted to provide a way that makes it more reusable and easier to maintain between APEX applications, they introduced plug-ins in APEX 4.0, this is now 10 years ago!
The result was that the awesome APEX community started to create and share plug-ins. Most people offer those plug-ins as open-source. Initially, there was an APEX plug-ins website maintained by Itium that had a catalog of all APEX plug-ins, but today you find the plug-ins catalog on apex.world.
There are many great plug-ins from people like Bartosz Ostrowski, Daniel Hochleitner, and Ronny Weiß to name a few, but there are also many plug-ins now that are built for a specific purpose but not maintained any longer. If you included one of those plug-ins in your app, you are on your own. But even if it's still maintained, you can't expect the author to add new features to the plug-in when you need them or when you upgrade the APEX version and it breaks.
Creating plug-ins and maintaining plug-ins takes a lot of time, especially when you want to think about the bigger picture and make them so good that they can be used in many different projects, they are fully documented and you have some sample code, so it's easy to include in any project, even when it's been a while since you last looked at it.
And this is how Plug-ins Pro was born... we want to take away the pain of having to build and maintain APEX plug-ins. These are not just plug-ins, those are rock-solid, state-of-the-art, professionally built plug-ins that behave just like native Oracle APEX components. They are accessible, translatable, universal theme ready, and most importantly fully documented, with many samples on how to use them AND supported! If you have an issue or you want an extension, you know who to go to.
With AOP we made printing and exporting of data easy in the Oracle database and APEX. With Plug-ins Pro we want to make extending the Oracle database and APEX easy!
Once I knew we wanted to bring high-quality, supported plug-ins to the APEX community, I knew we would need the best people to bring them to life. Bartosz Ostrowski joined the team in January of this year and began creating our awesome plug-ins. When we launched Plug-ins Pro we started with 3 highly demanded extensions for Oracle APEX:
- Client-Side Validation Pro
- Interactive Grid Checkbox Pro
- Interactive Grid Download (& Preview File) Pro
And over the last two months, we added two more plug-ins:
- Image Uploader Pro (* this is an enhanced version of the Dropzone Plug-in)
- Interactive JET Charts Pro
And we didn't go light on those plug-ins... the client-side validation plugin for example took over 400h to build!
We started from a requirements document:
We built the plug-in, tested it in on the different APEX versions (starting with 18.1), and finished off by creating extensive documentation and a full-fledged sample application!
The goal of Plug-ins Pro is to make you successful, so you can turn your ideas into reality with Oracle APEX and the Database.
Our goal is not to have all the possible plug-ins, instead, we want to create plug-ins that make a difference, and that would take a lot of time to build yourself. We have some plug-ins in the pipeline, some I'm super excited about and can't wait to share with you :) but just like with APEX Office Print (AOP), we especially want to listen to you, what are you searching for, and what would make a difference for you.
This is why on our website, you can submit ideas for new plug-ins and vote on already submitted ideas!
I will write some more blog posts on the plug-ins we offer, but if you already want to try them, head over to the Plug-ins Pro website and try them 1 month for free!
Last but not least... through September 30th, 2020, we are running an Early Adopter Special Offer. Show your support for Plug-ins Pro and help us get off the ground running! Get all the details at plug-ins-pro.com.
Categories: Development
Free Oracle Cloud: Custom Domain Name (URL) with your own ORDS on the Webserver
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
![](https://1.bp.blogspot.com/-4guEjpudKt4/X01MZyIIqCI/AAAAAAAAKs4/GflsYXyUjKQHD0R0pWmgZZFicrgqVMEIgCLcBGAsYHQ/s640/Screenshot%2B2020-08-30%2Bat%2B22.07.24.png)
One of the most popular posts in this series is how to Setup a web server on the Virtual Machine.
In that blog post we setup Nginx as our web server, and as a proxy in front of Oracle APEX running on the Autonomous Database Cloud, so we can serve our own domain name e.g. dgielis.com.
Since the release of ORDS 19.4.6 (and higher), Oracle supports that we run our own ORDS version in front of the Oracle Autonomous Database Cloud. This is really cool, for a couple of reasons:
- We can tune ORDS. Oracle limits our Free ATP to 20 concurrent connections, having our own ORDS we can allow more simultaneous connections to our database by changing the config files.
- We are able to create a reverse proxy on the same network as where ORDS is running, which will result in a more secure solution.
- We can have multiple ORDS running against the same database.
- We have more security options as we can choose where we run ORDS.
But before we go into the details on how to run ORDS on your own Compute VM and let it talk to the Autonomous Database Cloud, I also want to highlight a few consequences of doing this.
There are two main points of attention running your own ORDS in front of your APEX instance:
- You are responsible to keep your own ORDS version the same as the on the Autonomous Database Cloud. Whenever Oracle upgrades ORDS, you have to upgrade your own ORDS too.
- You have to update your images folder whenever a patch or upgrade is done for APEX.
Here's a small script I use to check the version of both Oracle APEX and ORDS:
select 'APEX' as product, version_no, api_compatibility, case when patch_applied = 'APPLIED' then (select listagg('Patch ' || to_char(patch_number) || ' (' || patch_version || ') installed on ' || installed_on, ', ') within group (order by installed_on) as patches from apex_patches) end as applied_patches from apex_release
union all
select 'ORDS' as product, ords.installed_version as version_no, null as api_compatibility, null as applied_patches from dual;
This results in:
You could automate the check, and in case something changed that it will email you for example.
Now, let's get started with the installation of ORDS on the same machine as our Nginx webserver.
# Log in as the opc user to the Compute VM and install Nginx and ORDS:
ssh 150.136.245.144 -l opc -i .ssh/oraclecloud
# connect as root
sudo su
# as a best practice, update all packages on Linux
![](https://1.bp.blogspot.com/-4guEjpudKt4/X01MZyIIqCI/AAAAAAAAKs4/GflsYXyUjKQHD0R0pWmgZZFicrgqVMEIgCLcBGAsYHQ/s640/Screenshot%2B2020-08-30%2Bat%2B22.07.24.png)
# load repo which holds ORDS
yum-config-manager --enable ol7_oci_included
# in case you get an error, add the repo first manually
/etc/yum.repos.d
vi oci-included-ol7.repo
[ol7_oci_included]
name=Oracle Software for OCI users on Oracle Linux $releasever ($basearch)
baseurl=https://yum$ociregion.oracle.com/repo/OracleLinux/OL7/oci/included/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
yum-config-manager --enable ol7_oci_included
# install ORDS
yum install -y ords
Let's also make sure we have the latest versions of SQLcl and the Oracle software and wallet.
Download SQLcl here and check this blog post about the software and wallet and let's make sure we can still connect from our compute instance to our Autonomous Database.
# uninstall old Oracle Instant client
yum remove oracle-instantclient18.5-tools.x86_64
# install latest Oracle Instant client
yum install oracle-instantclient19.8-basic.x86_64
# connect as oracle
su - oracle
# set environment variables
export PATH=/usr/lib/oracle/19.8/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64/lib
export TNS_ADMIN=/usr/lib/oracle/19.8/client64/lib/network/admin
# connect with SQLcl to see if we can connect from the VM to the DB
./sql admin@dbdimi_high
If for some reason something is not working any longer, follow the steps again in the blog post how to run sqlcl from the compute instance to ATP.
We first have to create an ORDS user we will connect at, and it won't be the normal ORDS_PUBLIC_USER as that is used by ATP itself. We will create an ORDS_PUBLIC_USER2 as specified in the Installing and Configuring Customer Managed ORDS on Autonomous Database documentation.
CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY "changeme";
GRANT "CONNECT" TO "ORDS_PUBLIC_USER2";
BEGIN
ORDS_ADMIN.PROVISION_RUNTIME_ROLE(
p_user => 'ORDS_PUBLIC_USER2',
p_proxy_enabled_schemas => TRUE);
END;
/
Now let's configure ORDS to connect to this user in the database:
# make a base64 string of the zip
mkdir /opt/oracle/ords/walletcd /opt/oracle/ords/wallet
cp /tmp/wallet_DBDIMI.zip .
base64 -w 0 wallet_DBDIMI.zip > wallet_DBDIMI.zip.b64
WALLET_BASE64=`cat wallet_DBDIMI.zip.b64`
# create the apex_pu.xml and defaults.xml configuration files for ORDS
cat << EOF > /opt/oracle/ords/config/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="db.username">ORDS_PUBLIC_USER2</entry>
<entry key="db.password">!changeme</entry>
<entry key="db.wallet.zip.service">dbdimi_low</entry>
<entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF
cat << EOF > /opt/oracle/ords/config/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="plsql.gateway.enabled">true</entry>
<entry key="jdbc.InitialLimit">10</entry>
<entry key="jdbc.MaxLimit">20</entry>
</properties>
EOF
Let's start ORDS manually first, so we can see if there are any errors:
cd /opt/oracle/ords
java -jar ords.war standalone
Note: In case you get java.lang.OutOfMemoryError: Java heap space, you can fix that by doing:
export JAVA_OPTIONS=-Xmx512M
In July I got this error, when I tried again last week I didn't anymore.
Copy Oracle APEX images folder.
Download Oracle APEX and upload the zip file with your favorite SFTP program to your compute instance. E.g. I use Transmit on OSX or you can use wget or something else.
# Unzip the file in the /opt/oracle folder
unzip /tmp/apex_20.1_en.zip -d /opt/oracle/
You need to make sure your APEX Images folder is in sync with the version of APEX running on ATP.
Typically Oracle will apply some patches, so download from Oracle Support, the patch for Oracle APEX and download it to your Compute Instance:
# Unzip the file in the /tmp
unzip p30990551_2010_Generic.zip
# Overwrite the files from the original Oracle APEX folder
cp -rf /tmp/30990551/images /opt/oracle/apex
# Edit the standalone.properties to add the reference to the images folder
vi /opt/oracle/ords/config/ords/standalone/standalone.properties
# Add
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images
Restart ORDS again, and once we are sure all is working, let's start ORDS as a service and enable it to autostart on reboot:
# make sure you are root user
exit
# run ORDS service and enable with startup
systemctl start ords
systemctl enable ords
Next, we want to configure Nginx as a reverse proxy in front of ORDS.
ORDS by default is running on port 8080. You may want to read my previous blog post on how to get started with Nginx. I didn't configure the domain yet, but I just wanted to access the IP.
The different bit is :
vi /etc/nginx/conf.d/dgielis.com.conf
Add the following:
server {
listen 80;
listen [::]:80;
server_name 150.136.245.144;
root /usr/share/nginx/html/dgielis.com;
index index.html;
try_files $uri /index.html;
location /i/20.1.0.00.13/ {
alias /opt/oracle/apex/images/;
}
location /ords/ {
proxy_pass http://localhost:8080/ords/;
proxy_redirect off;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
}
Next, you want to reload Nginx:
nginx -s reload
The first time I tried to access http://150.136.245.144/ords/ I got a bad gateway error.
Looking at the logs I saw what was going on:
tail -30 /var/log/nginx/error.log
To fix the issue I did:
cat /var/log/audit/audit.log | grep nginx | grep denied | audit2allow -M mynginx
semodule -i mynginx.pp
And there we go... it worked! Now we can access Oracle APEX with our own ORDS :)
Note: instead of using Nginx, you can also configure a Load Balancer in the Oracle Cloud.
Adrian did a nice blog post on how to do that.
There you go... now you can choose to have your preferred domain name (custom URL) with your own ORDS in the Oracle Cloud.
Categories: Development
More info on Oracle Always Free APEX upgrades
This post is a continuation of the series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
If you do nothing your APEX instance will be upgraded as soon as possible, but you can decide to defer the update up to 45 days.
Here I changed it to defer the update, up to 45 days this can be done before it performs the upgrade automatically.
With the release of APEX 19.2 you couldn't really decide when Oracle would upgrade your APEX version in the Always Free Oracle Cloud. But this is now changed. From now on you can decide when Oracle upgrades your Oracle APEX version.
This is the email I received a few days ago:
In order to check what the settings are on your instance, log in to your Oracle Cloud account.
Go to Autonomous Transaction Processing and click on your database, in my case DBDIMI. From there click on Tools and hit Open APEX:
You might have a direct link to your Oracle APEX instance, you can click on that too.
Login to your INTERNAL workspace, with the ADMIN user:
On the right-hand side it will tell you when an update is being done for Oracle APEX:
By default, it will update automatically when Oracle APEX is made available for your platform.
But if you click on the gear icon you can change it:
You find some more frequently asked questions in this blog post.
Categories: Development
Oracle Always Free Oracle Database upgrades
This post is a continuation of the series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
During APEX@Home I presented "Bringing the light in your Always FREE Autonomous Cloud".
You can watch the recorded presentation or look at the slides. During my presentation, I mentioned how to upgrade the database version you are on, opt-in, or wait for Oracle to upgrade your instance automatically. As I received an email today, I thought to write a quick blog post about it.
Oracle comes with new releases of the Oracle Database yearly and for Oracle Application Express (APEX) even twice a year. The Oracle Autonomous Cloud will upgrade the instance by itself, both for the database as for APEX. You don't really have control when this happens, but Oracle will let you know. This email I received today:
![](https://1.bp.blogspot.com/-ptQPJski-CM/XtTEbdnA-8I/AAAAAAAAKeo/CczBIBcCLaU9_taVGf-QwpaNMuqEbZgWACLcBGAsYHQ/s640/Screenshot%2B2020-06-01%2Bat%2B11.02.22.png)
BUT, if you are really eager to get the newer functionalities sooner, this blog post shows you how.
When you log in to the Oracle Cloud and go to the details of your Autonomous Database Cloud, you will find under Actions - Create Clone.
Cloning is a great way to copy your entire instance and try different things with it. When doing for example a new project, you can start with the always free service and when development is done, clone the instance as a production instance. You can choose to have another free cloud or upgrade to a paid service.
In this case, I will use the Clone feature to upgrade my database to 19c myself.
You can do a full clone or metadata clone. I typically use a full clone, so all is there, tables, data and Oracle APEX apps.
Hitting the Clone button and the instance is provisioned:
It takes a couple of minutes to complete but after that, there it is, your upgraded instance... well it's not really upgraded as you have both an 18c and 19c version now.
I confirmed the database with all my data and all my APEX workspaces and apps where there :)
Pretty cool!
During APEX@Home I presented "Bringing the light in your Always FREE Autonomous Cloud".
You can watch the recorded presentation or look at the slides. During my presentation, I mentioned how to upgrade the database version you are on, opt-in, or wait for Oracle to upgrade your instance automatically. As I received an email today, I thought to write a quick blog post about it.
Oracle comes with new releases of the Oracle Database yearly and for Oracle Application Express (APEX) even twice a year. The Oracle Autonomous Cloud will upgrade the instance by itself, both for the database as for APEX. You don't really have control when this happens, but Oracle will let you know. This email I received today:
![](https://1.bp.blogspot.com/-ptQPJski-CM/XtTEbdnA-8I/AAAAAAAAKeo/CczBIBcCLaU9_taVGf-QwpaNMuqEbZgWACLcBGAsYHQ/s640/Screenshot%2B2020-06-01%2Bat%2B11.02.22.png)
BUT, if you are really eager to get the newer functionalities sooner, this blog post shows you how.
When you log in to the Oracle Cloud and go to the details of your Autonomous Database Cloud, you will find under Actions - Create Clone.
In this case, I will use the Clone feature to upgrade my database to 19c myself.
You can do a full clone or metadata clone. I typically use a full clone, so all is there, tables, data and Oracle APEX apps.
When you clone, depending the time you have an option to select the database version. Today I could only select the 19c version, which is exactly what I wanted:
Hitting the Clone button and the instance is provisioned:
It takes a couple of minutes to complete but after that, there it is, your upgraded instance... well it's not really upgraded as you have both an 18c and 19c version now.
I confirmed the database with all my data and all my APEX workspaces and apps where there :)
Pretty cool!
Categories: Development
Today United Codes changes the way you look at your Oracle APEX apps
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:
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.
Monitoring
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
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:
- an automated documentation generation tool
- a monitoring tool
- a quality assurance tool
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.
Monitoring
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 :)
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
Categories: Development
ORDS: The database user for the connection pool named |apex|rt|, is not authorized to proxy to the schema
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 :)
Categories: Development
A new look, a new name...
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.
![](https://1.bp.blogspot.com/-7IjbWk1ZR2E/XrasWeQu7-I/AAAAAAAAKZU/zzkNYo8HYL0MXvxL4JNYTbSVR-3ANE3cwCLcBGAsYHQ/s320/logo-apex-office.png)
![](https://1.bp.blogspot.com/-TzlXUnY1loM/XrasWdg6WuI/AAAAAAAAKZQ/eCOiRmMeiswrGNXrz1eH8I6-fg53L-hvgCLcBGAsYHQ/s320/logo-media-ext.png)
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.
2030
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.
![](https://1.bp.blogspot.com/-7IjbWk1ZR2E/XrasWeQu7-I/AAAAAAAAKZU/zzkNYo8HYL0MXvxL4JNYTbSVR-3ANE3cwCLcBGAsYHQ/s320/logo-apex-office.png)
![](https://1.bp.blogspot.com/-TzlXUnY1loM/XrasWdg6WuI/AAAAAAAAKZQ/eCOiRmMeiswrGNXrz1eH8I6-fg53L-hvgCLcBGAsYHQ/s320/logo-media-ext.png)
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.
2030
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.
Categories: Development
Configuring AOP as Print Server in Oracle APEX 20.1
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
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. Make sure you are running AOP 20.1 or above, and for the Script, you enter /apex/
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!
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:
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:
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!
Categories: Development
Fix ORDS issue: No pools configured yet
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
Categories: Development
Oracle APEX 20.1 installed with only 2 mins downtime
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/20.1.0.00.13/
I ran the following command to allow APEX apps accessing, for example, AOP or other 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_200100',
principal_type => xs_acl.ptype_db));
END;
/
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!
Update 27-APR-2020: Kris Rice sent me a message that there is no need to stop ORDS. It detects when APEX is upgrading and suspends traffic.
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/20.1.0.00.13/
I ran the following command to allow APEX apps accessing, for example, AOP or other 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_200100',
principal_type => xs_acl.ptype_db));
END;
/
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!
Update 27-APR-2020: Kris Rice sent me a message that there is no need to stop ORDS. It detects when APEX is upgrading and suspends traffic.
Categories: Development
APEX Media Extension (AME) Publicly Available
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!
In our first release of AME, you will find the following features:
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:
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!
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)
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
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!
Categories: Development
Free Oracle Cloud: 19. Oracle APEX upgrades (19.1 to 19.2)
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.
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.
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.
Categories: Development
Print PDF from Oracle APEX to Local or Remote Printer
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.
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.
Categories: Development
Important update of ORDS... release 19.4
A few days ago Oracle released Oracle REST Data Services (ORDS) 19.4.
In my opinion, it's a major release and most likely worth investigating to upgrade for everybody. For me, the following 3 improvements are worth the upgrade:
Performance of REST APIs
The performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. You can read more about this in the readme.
We have a customer hitting ORDS through a mobile app really hard (potentially 130 000 end-users), so any improvements in this area are awesome for those types of customers.
Removal of PDF Generation Support
As previously advised in the ORDS 18.4.0 Release Notes, the Apache FOP based functionality to produce PDF Reports from Oracle Application Express (APEX) has been removed in this release. This means that if you still want to print or export files in Oracle APEX, you most likely want to look at using APEX Office Print (AOP). AOP is the most integrated printing and exporting solution for Oracle Application Express and the defacto standard these days. It comes with an AOP Report which is similar to what ORDS provided to APEX: based on the print attributes it generates a PDF. But, AOP gives you tons more features and flexibility when you want to print and export from APEX!
When you install the AOP Plug-in, choose your own template, for example, and look at the different Data Types that are available.
SQL Developer Web
This release sees the introduction of Oracle SQL Developer Web, an ORDS hosted web application giving Oracle Database users an interface for executing queries and scripts, creating and altering database objects, building data models, accessing Performance Hub, and viewing database activity.
After setting the following properties in default.xml:
You can access SQL Developer Web through:
http(s)://your_server:your_port/ords/sql-developer
You log in with a database user and password who is REST enabled. In this script I REST enable the user DIMI:
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'DIMI',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'dimi',
p_auto_rest_auth => FALSE);
commit;
END;
/
This is what SQL Developer Web looks like, a browser-based version of SQL Developer (desktop) and a better version of what you find in SQL Workshop in APEX (although not all features are in yet, for example, I miss editing of a record):
SQL Developer Web includes another jewel... a Database Dashboard and Activity Monitoring!
When you log in with a user with the PDB_DBA role, you get a whole new section:
You find also more information in the ORDS 19.4 documentation.
Jeff Smith wrote a nice blog post about how to get started with SQL Developer Web too.
Really nice release!
In my opinion, it's a major release and most likely worth investigating to upgrade for everybody. For me, the following 3 improvements are worth the upgrade:
Performance of REST APIs
The performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. You can read more about this in the readme.
We have a customer hitting ORDS through a mobile app really hard (potentially 130 000 end-users), so any improvements in this area are awesome for those types of customers.
Removal of PDF Generation Support
As previously advised in the ORDS 18.4.0 Release Notes, the Apache FOP based functionality to produce PDF Reports from Oracle Application Express (APEX) has been removed in this release. This means that if you still want to print or export files in Oracle APEX, you most likely want to look at using APEX Office Print (AOP). AOP is the most integrated printing and exporting solution for Oracle Application Express and the defacto standard these days. It comes with an AOP Report which is similar to what ORDS provided to APEX: based on the print attributes it generates a PDF. But, AOP gives you tons more features and flexibility when you want to print and export from APEX!
When you install the AOP Plug-in, choose your own template, for example, and look at the different Data Types that are available.
SQL Developer Web
This release sees the introduction of Oracle SQL Developer Web, an ORDS hosted web application giving Oracle Database users an interface for executing queries and scripts, creating and altering database objects, building data models, accessing Performance Hub, and viewing database activity.
After setting the following properties in default.xml:
You can access SQL Developer Web through:
http(s)://your_server:your_port/ords/sql-developer
You log in with a database user and password who is REST enabled. In this script I REST enable the user DIMI:
BEGIN
ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => 'DIMI',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'dimi',
p_auto_rest_auth => FALSE);
commit;
END;
/
This is what SQL Developer Web looks like, a browser-based version of SQL Developer (desktop) and a better version of what you find in SQL Workshop in APEX (although not all features are in yet, for example, I miss editing of a record):
SQL Developer Web includes another jewel... a Database Dashboard and Activity Monitoring!
When you log in with a user with the PDB_DBA role, you get a whole new section:
You find also more information in the ORDS 19.4 documentation.
Jeff Smith wrote a nice blog post about how to get started with SQL Developer Web too.
Really nice release!
Categories: Development
Free Oracle Cloud: 18. Monitoring your website and APEX app
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
As more and more people are putting their production apps on the Always Free Oracle Cloud, it's probably a good idea to start monitoring your instance. (but remember this is a no-SLA environment)
Over the last few weeks, it's happened to me - my EU test database was stopped and one of my compute instances was accidentally dropped and restored. Oracle notified me and took action themselves:
If I had monitoring on, I would have seen this myself. The above case I couldn't solve on my own, but it could happen that some software you are running goes down, or something else makes your site/app unavailable due to your own code. If that happens you want to take action and verify if things are ok.
There are many monitoring tools out there, some are paid, some are free. I've used Pingdom and UptimeRobot. It takes only a minute to set it up. I'll show how to do it with UptimeRobot.
Sign-up at uptimerobot.com
Click the New Monitor button and specify the URL you want to monitor.
Add a type of connection where you want to be notified and you are done!
You can simulate what happens if you go into your compute instance and stop the webserver.
nginx -s stop
Depending on the monitoring interval you will get an email within 5 minutes when your URL can't be reached.
You can also log in and see in the dashboard how much uptime you had and when you were down:
nginx -s stop
Depending on the monitoring interval you will get an email within 5 minutes when your URL can't be reached.
You can restart your web server by doing:
systemctl restart nginx
A few minutes later UptimeRobot will notify you all is good again :)
And surely in the dashboard, we are UP again:
Happy monitoring!
Categories: Development
Free Oracle Cloud: 17. Configure domain to redirect to APEX app
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
In a previous blog post, we configured the webserver on our Compute VM Instance. We added a website and configured the domain dgielis.com to point to this instance.
I got many requests on how to point the domain name to a specific Oracle APEX app. This is what I will cover in this blog post.
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 changing to the root user, as we want to configure the web server. 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
# open the configuration file we created in the previous blog post
vi /etc/nginx/conf.d/dgielis.com.conf
# add following
location / {
rewrite ^/$ /ords/f?p=101:LOGIN_DESKTOP:0 permanent;
}
# save and quit (:wq)
My config file looks now like this:
# to test Nginx configuration
nginx -t
# to restart Nginx
nginx -s reload
That's it.
In a previous blog post, we configured the webserver on our Compute VM Instance. We added a website and configured the domain dgielis.com to point to this instance.
I got many requests on how to point the domain name to a specific Oracle APEX app. This is what I will cover in this blog post.
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 changing to the root user, as we want to configure the web server. 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
# open the configuration file we created in the previous blog post
vi /etc/nginx/conf.d/dgielis.com.conf
# add following
location / {
rewrite ^/$ /ords/f?p=101:LOGIN_DESKTOP:0 permanent;
}
# save and quit (:wq)
My config file looks now like this:
# to test Nginx configuration
nginx -t
# to restart Nginx
nginx -s reload
That's it.
Categories: Development
The best way to be productive with APEX Office Print (AOP)
We often get the question of "how to be the most productive creating a template for APEX Office Print (AOP)".
Here are two examples of people asking in different ways:
My short answer: I recommend to use the Fast Template Switcher in our AOP Sample App or when you use the on-premises version of AOP, connect to the server and use the AOP Web Editor.
In this blog post, I will do a step-by-step guide on how I believe you will be most performant building your templates and using them in AOP.
If you didn't install APEX Office Print (AOP) yet, please read my blog post Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
Now, we will add AOP to a button on a page in an APEX app.
Dynamic Action Plug-in
When you have a button on the page, right-click on it and choose "Create Dynamic Action".
Give it a name and as Action, specify APEX Office Print (AOP) - DA [Plug-in]
Data
The most important part of any report is the data it contains. So, the first step is to define where AOP can find all the data you want to use in the report. AOP gives you the ability to get all your data through a hierarchical SQL statement by using the cursor() or JSON syntax. For example, if we want to query all the orders and order lines (details) of a given customer we can specify in the Data Source:
select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor(
select
o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor(
select
p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price",
APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image",
40 as "image_max_width"
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from demo_orders o
where c.customer_id = o.customer_id
) "orders"
from demo_customers c
where customer_id = :P1_CUSTOMER_ID
) as "data"
from dual
In case your data is more complex than you can define in a hierarchical SQL statement, you can also specify a PL/SQL Function or a JSON data source.
AOP Template
Now you want this data in a specific look and feel. AOP allows you to specify your own template written in Word, Excel, Powerpoint, HTML, and Text. A good way to start your template is by using the AOP Template (which is selected by default). This means you don't specify your own template yet, but let AOP generate a starting template for you, based on the data you provided. AOP can generate a starter template in Word, Excel, and HTML.
Based on your data, it will generate a {tag} for every column you have. When AOP sees there are multiple records (a table), it will generate a loop statement {#tag}{/tag} as well.
Now you can iterate over your template to make it exactly as you want it to look like. Just rearrange the {tags} to the specific positions where you want your data to be. Apart from that, you are in Word (or Excel, Powerpoint, ...) and have access to all of the native Word features to create a gorgeous look and feel. If you already have a document, you can also copy and paste the tags from the AOP Template into your existing document.
What I see, is that people adjust the template, upload the template, adjust, upload, adjust, upload over and over. There's actually a much faster way to iterate through template development.
Local Debug
Behind the scenes, the AOP Plug-in generates a JSON file that is being sent to the AOP Server.
To get this JSON, you can go into Shared Components > Component Settings > APEX Office Print and set Debug to Local. This will enable AOP Debug for the entire application. If you just want to put AOP Debug on for the current button, add the following to the Init PL/SQL Code of the AOP Dynamic Action:
aop_api_pkg.g_debug := aop_api_pkg.c_debug_local;
This is what you should see in the Dynamic Action:
When you click the button, you will get the JSON file.
Quickly changing Templates
With AOP we ship an AOP Sample App. This is an APEX application which showcases many features of APEX Office Print:
In the Debugging section, you find a Quick Template Changer link:
The Quick Template Changer allows you to quickly try new templates based on an AOP JSON file.
You drag the JSON file in the first box under Exported JSON.
The JSON will be parsed and the content of the JSON is shown in the JSON Data field.
Next, you drag your template in the Template section and finally, you select the Output you want and hit the Process button.
You can now remove the template and drag-and-drop a new version of the template and hit the Process button again. This way you can quickly see the result while making changes to the template.
Web Editor in AOP On-Premises version
If you download the AOP On-Premises version and run the AOP Server (which is one executable) locally, or you navigate to the server URL where your AOP Server is running, you will see the AOP Web Editor.
This editor is really powerful and has more features than the Quick Template Changer you find in the AOP Sample App. Just as before you drag-and-drop the JSON file in the Exported JSON section.
The AOP Web Editor will parse the JSON and will show a link to the template to Download (unnamed.docx). If you want to change the Template, you just drag-and-drop another template in the Template section. On this screen, you can even prepend and append files and add sub-templates. If you don't like to always select a file from the file system, you can select all files or even a directory and drag-and-drop it entirely in the File cache section, so you can swap files even quicker!
Select the Output and hit the Process button, and presto, you see the output with the (new) template!
But that is not all, you can also change the Data on the fly by going into the Data tab:
Or see the new resulting JSON file after changing the data and template:
In case you want some examples, hit the Load Sample button and select a sample.
I really believe the AOP Web Editor will help you a lot in your development of AOP Reports.
This AOP Web Editor app is actually a nice showcase that AOP can be used with any technology. The Web Editor is written with React.js.
Hope this helps you to be even more productive with AOP!
Here are two examples of people asking in different ways:
My short answer: I recommend to use the Fast Template Switcher in our AOP Sample App or when you use the on-premises version of AOP, connect to the server and use the AOP Web Editor.
In this blog post, I will do a step-by-step guide on how I believe you will be most performant building your templates and using them in AOP.
If you didn't install APEX Office Print (AOP) yet, please read my blog post Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
Now, we will add AOP to a button on a page in an APEX app.
Dynamic Action Plug-in
When you have a button on the page, right-click on it and choose "Create Dynamic Action".
Give it a name and as Action, specify APEX Office Print (AOP) - DA [Plug-in]
Data
The most important part of any report is the data it contains. So, the first step is to define where AOP can find all the data you want to use in the report. AOP gives you the ability to get all your data through a hierarchical SQL statement by using the cursor() or JSON syntax. For example, if we want to query all the orders and order lines (details) of a given customer we can specify in the Data Source:
select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor(
select
o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor(
select
p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price",
APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image",
40 as "image_max_width"
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from demo_orders o
where c.customer_id = o.customer_id
) "orders"
from demo_customers c
where customer_id = :P1_CUSTOMER_ID
) as "data"
from dual
In case your data is more complex than you can define in a hierarchical SQL statement, you can also specify a PL/SQL Function or a JSON data source.
AOP Template
Now you want this data in a specific look and feel. AOP allows you to specify your own template written in Word, Excel, Powerpoint, HTML, and Text. A good way to start your template is by using the AOP Template (which is selected by default). This means you don't specify your own template yet, but let AOP generate a starting template for you, based on the data you provided. AOP can generate a starter template in Word, Excel, and HTML.
Based on your data, it will generate a {tag} for every column you have. When AOP sees there are multiple records (a table), it will generate a loop statement {#tag}{/tag} as well.
Now you can iterate over your template to make it exactly as you want it to look like. Just rearrange the {tags} to the specific positions where you want your data to be. Apart from that, you are in Word (or Excel, Powerpoint, ...) and have access to all of the native Word features to create a gorgeous look and feel. If you already have a document, you can also copy and paste the tags from the AOP Template into your existing document.
What I see, is that people adjust the template, upload the template, adjust, upload, adjust, upload over and over. There's actually a much faster way to iterate through template development.
Local Debug
Behind the scenes, the AOP Plug-in generates a JSON file that is being sent to the AOP Server.
To get this JSON, you can go into Shared Components > Component Settings > APEX Office Print and set Debug to Local. This will enable AOP Debug for the entire application. If you just want to put AOP Debug on for the current button, add the following to the Init PL/SQL Code of the AOP Dynamic Action:
This is what you should see in the Dynamic Action:
When you click the button, you will get the JSON file.
Quickly changing Templates
With AOP we ship an AOP Sample App. This is an APEX application which showcases many features of APEX Office Print:
In the Debugging section, you find a Quick Template Changer link:
The Quick Template Changer allows you to quickly try new templates based on an AOP JSON file.
You drag the JSON file in the first box under Exported JSON.
The JSON will be parsed and the content of the JSON is shown in the JSON Data field.
Next, you drag your template in the Template section and finally, you select the Output you want and hit the Process button.
You can now remove the template and drag-and-drop a new version of the template and hit the Process button again. This way you can quickly see the result while making changes to the template.
Web Editor in AOP On-Premises version
If you download the AOP On-Premises version and run the AOP Server (which is one executable) locally, or you navigate to the server URL where your AOP Server is running, you will see the AOP Web Editor.
This editor is really powerful and has more features than the Quick Template Changer you find in the AOP Sample App. Just as before you drag-and-drop the JSON file in the Exported JSON section.
The AOP Web Editor will parse the JSON and will show a link to the template to Download (unnamed.docx). If you want to change the Template, you just drag-and-drop another template in the Template section. On this screen, you can even prepend and append files and add sub-templates. If you don't like to always select a file from the file system, you can select all files or even a directory and drag-and-drop it entirely in the File cache section, so you can swap files even quicker!
Select the Output and hit the Process button, and presto, you see the output with the (new) template!
But that is not all, you can also change the Data on the fly by going into the Data tab:
Or see the new resulting JSON file after changing the data and template:
In case you want some examples, hit the Load Sample button and select a sample.
I really believe the AOP Web Editor will help you a lot in your development of AOP Reports.
This AOP Web Editor app is actually a nice showcase that AOP can be used with any technology. The Web Editor is written with React.js.
Hope this helps you to be even more productive with AOP!
Categories: Development
Alternative for Oracle Multimedia: APEX Media Extension
If you are reading this blog post you are probably searching for an alternative for the deprecated multimedia (or intermedia) feature of the Oracle Database... and you are in the right post as APEX Media Extension is that replacement!
Just like you, I loved the Oracle Multimedia feature in the Oracle Database. I used the feature in many different applications, mostly related to images. For example, when some students upload images of their work in an Oracle APEX app, or teachers upload images to include in tests, I made thumbnails of the images and resized them so they fit really nicely on the page.
Unfortunately, Oracle announced starting in Oracle Database 18c, Oracle Multimedia is deprecated. You can still use it, but you know it will go away at some point... and that is what happened with Oracle Database 19c. Oracle Multimedia is now desupported and doesn't even work anymore. The Oracle Multimedia packages are still there, so your code is still compiling and valid, but it's not doing anything anymore.
Just like you and others, I reviewed and tested different alternatives, but to make a long story short, I wasn't completely happy with any of them. So we at APEX R&D decided to offer a solution that is easy to use and fully supported. There's a PL/SQL API so you can use it just like Oracle Multimedia from the Oracle Database. As we love Oracle APEX, we also provide two APEX Plug-ins: one plug-in which sits on top of the PL/SQL API (which is a server-based solution) and another one which is a pure client-side (JavaScript) implementation. Our goal is that it's as easy as possible to integrate this solution into your Oracle Database and/or APEX app. We named this solution APEX Media Extension and it will be available before the end of the year (2019).
![](https://1.bp.blogspot.com/-oQ8An_lEtuQ/Xe-Oj1FhIgI/AAAAAAAAKI8/LVKRJQXjGwMLgP2dfUTNIyqOp7ZMmeCyQCLcBGAsYHQ/s640/apex_media_extension.png)
In this initial release, we've focussed on converting media, such as:
BUT that is not all... we decided to include this functionality also in APEX Office Print (AOP) (Gold and Enterprise versions)! So, if you already use AOP, in our upcoming version you are fully covered already :)
Interested? Please leave your email on the APEX Media Extension website and you will be first to know when it's released.
Just like you, I loved the Oracle Multimedia feature in the Oracle Database. I used the feature in many different applications, mostly related to images. For example, when some students upload images of their work in an Oracle APEX app, or teachers upload images to include in tests, I made thumbnails of the images and resized them so they fit really nicely on the page.
Unfortunately, Oracle announced starting in Oracle Database 18c, Oracle Multimedia is deprecated. You can still use it, but you know it will go away at some point... and that is what happened with Oracle Database 19c. Oracle Multimedia is now desupported and doesn't even work anymore. The Oracle Multimedia packages are still there, so your code is still compiling and valid, but it's not doing anything anymore.
Just like you and others, I reviewed and tested different alternatives, but to make a long story short, I wasn't completely happy with any of them. So we at APEX R&D decided to offer a solution that is easy to use and fully supported. There's a PL/SQL API so you can use it just like Oracle Multimedia from the Oracle Database. As we love Oracle APEX, we also provide two APEX Plug-ins: one plug-in which sits on top of the PL/SQL API (which is a server-based solution) and another one which is a pure client-side (JavaScript) implementation. Our goal is that it's as easy as possible to integrate this solution into your Oracle Database and/or APEX app. We named this solution APEX Media Extension and it will be available before the end of the year (2019).
![](https://1.bp.blogspot.com/-oQ8An_lEtuQ/Xe-Oj1FhIgI/AAAAAAAAKI8/LVKRJQXjGwMLgP2dfUTNIyqOp7ZMmeCyQCLcBGAsYHQ/s640/apex_media_extension.png)
In this initial release, we've focussed on converting media, such as:
- resizing images
- cropping images
- adding watermarks to images
- compressing images
- changing image formats (jpg, png, ...)
BUT that is not all... we decided to include this functionality also in APEX Office Print (AOP) (Gold and Enterprise versions)! So, if you already use AOP, in our upcoming version you are fully covered already :)
Interested? Please leave your email on the APEX Media Extension website and you will be first to know when it's released.
Categories: Development
Free Oracle Cloud: 16. Renewing Let's Encrypt certificate
When you followed along with my series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud you most likely will have gotten an email from Let's Encrypt that your certificate is due for renewal.
To check your certificate, go to your site in a browser and click on the lock:
Yep, corresponds to the email, in 10 days my certificate will expire.
Let's get this fixed! So connect with ssh to your Compute instance (see the previous post in the series if you forgot those commands).
When I connect to my machines I typically first run yum update to get the latest packages installed so we are current with security patches, or just, in general, keep up with the latest software.
sudo su (to become root)
yum update
Time to renew our certificate. It's very easy to do, run
certbot certonly
Ha! Apparently not so simple after all?! We got an error. This might happen when packages are incompatible. Normally yum should take care of that, but as we installed Certbot with Pip, let's upgrade all those components too.
pip install -U pip
As the above error indicates an issue with cryptography, I will update that too.
pip install cryptography --upgrade
Now, let's try to renew our certificate again:
certbot certonly
and type your domain name(s):
Cool, that worked... your certificate is now updated.
To get the new certificate active we restart the webserver (after testing if all is ok):
nginx -t
nginx -s reload
Finally, we check the certificate in a browser to see if the new one is there:
All done, time to relax again for a couple of months.
Update: Morten made the remark why not to automate the renewal:
It's a great comment! When you look at the Certbot instructions, it actually gives you the steps to auto-renew, so you might have already done that. I actually have this running on some of our servers, but I didn't include this step in my initial blog post when we configured the webserver.
So, in case you didn't set up the automatic renewal, or the automatic renewal failed, you have the steps above to fix it.
To check your certificate, go to your site in a browser and click on the lock:
Yep, corresponds to the email, in 10 days my certificate will expire.
Let's get this fixed! So connect with ssh to your Compute instance (see the previous post in the series if you forgot those commands).
When I connect to my machines I typically first run yum update to get the latest packages installed so we are current with security patches, or just, in general, keep up with the latest software.
sudo su (to become root)
yum update
Time to renew our certificate. It's very easy to do, run
certbot certonly
Ha! Apparently not so simple after all?! We got an error. This might happen when packages are incompatible. Normally yum should take care of that, but as we installed Certbot with Pip, let's upgrade all those components too.
pip install -U pip
As the above error indicates an issue with cryptography, I will update that too.
pip install cryptography --upgrade
Now, let's try to renew our certificate again:
certbot certonly
and type your domain name(s):
Cool, that worked... your certificate is now updated.
To get the new certificate active we restart the webserver (after testing if all is ok):
nginx -t
nginx -s reload
Finally, we check the certificate in a browser to see if the new one is there:
All done, time to relax again for a couple of months.
Update: Morten made the remark why not to automate the renewal:
It's a great comment! When you look at the Certbot instructions, it actually gives you the steps to auto-renew, so you might have already done that. I actually have this running on some of our servers, but I didn't include this step in my initial blog post when we configured the webserver.
So, in case you didn't set up the automatic renewal, or the automatic renewal failed, you have the steps above to fix it.
Categories: Development
Free Oracle Cloud: 15. The request could not be mapped to any database
This post is the last post of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
At some point you might face the following message: "The request could not be mapped to any database":
Oracle is monitoring usage on your Always Free Account and whenever it finds there's no activity for 7 days, it will stop your database automatically. It will preserve the data in the database, but it won't be accessible anymore.
To fix the issue, log in to your Oracle Cloud Account and go to your Autonomous Database:
You will see the database is in a stopped state. Click the Start button:
The state will change to Starting...
And after a minute it becomes available again:
The above behavior is written in the end-user documentation:
But this week there were some people complaining that although they had activity, their database was stopped anyway. I witnessed the same behavior in my account, so I reached out to Oracle and they confirmed their code to identify inactivity, is not properly accounting for APEX/ORDS usage. They are already working on a fix, which they hope to apply very soon. I will update this post when I get confirmation the fix is in the data centers.
At some point you might face the following message: "The request could not be mapped to any database":
Oracle is monitoring usage on your Always Free Account and whenever it finds there's no activity for 7 days, it will stop your database automatically. It will preserve the data in the database, but it won't be accessible anymore.
To fix the issue, log in to your Oracle Cloud Account and go to your Autonomous Database:
You will see the database is in a stopped state. Click the Start button:
The state will change to Starting...
And after a minute it becomes available again:
The above behavior is written in the end-user documentation:
Inactivity Monitoring and Database Stoppage
Persistently inactive Always Free Autonomous Databases are detected and handled as follows:
- After being inactive for 7 days, the database will be stopped automatically, preserving its stored data. Inactivity measurements leading up to 7 days are based on database connections. Successfully making a SQL*Net or HTTPS connection resets these measurements to zero.
- A database that is automatically or manually stopped and stays inactive for 90 days, cumulative, may be reclaimed and permanently deleted. Inactivity measurements leading up to 90 days are based on the database being inactive or in the stopped state. Starting a stopped database resets these measurements to zero.Start an Always Free Autonomous Database by clicking the Start button on the Oracle Cloud Infrastructure console. Start a stopped Always Free Autonomous Database before 90 days to avoid losing access to its data.
But this week there were some people complaining that although they had activity, their database was stopped anyway. I witnessed the same behavior in my account, so I reached out to Oracle and they confirmed their code to identify inactivity, is not properly accounting for APEX/ORDS usage. They are already working on a fix, which they hope to apply very soon. I will update this post when I get confirmation the fix is in the data centers.
Categories: Development