Better Logic for Better Solutions
Feed aggregator
Indexing The Oracle Autonomous Data Warehouse (Autobahn)
When the Autonomous Data Warehouse was initially released in March 2018, one of the notable restrictions was that it did not support typical Data Warehouse performance related structures such as Indexes, Partitioning or Materialized Views. The platform was deemed efficient enough to not require these features, which would unnecessarily complicate the autonomous environment. In September […]
Monitoring your Oracle APEX environment; when is a good time to do an upgrade?
Yesterday Oracle released Oracle APEX 19.1. We typically upgrade our environment within a week.
become
had The question is always, when is a good time to upgrade?
eva
As we are hosting multiple applications and sites in our environment, we want to find a spot with minimal impact to our customers. For some applications which are only accessed by some people in Belgium, it's very easy to schedule an upgrade at night as the chances are not high people accessing the app. But in the case of APEX Office Print, we have customers all over the world in different timezones. So they might want to look at their dashboard or download the latest version at any moment in time.
For our AOP service itself, we have an internal policy we don't allow downtime. We load balance, so even when performing upgrades of AOP, there will always be a server available to handle the requests. For our Oracle database and APEX environment, we are fine with a few minutes downtime while upgrading the server.
In Oracle APEX itself, when you go to Monitor Activity - Page Views by User by Hour you get a graphical overview when your applications are being used:
This screen works well for a given workspace. If you want to look cross workspaces, for your entire APEX instance, you could build something yourself similar to the above. An overview like that would give you an idea based on historical usage which hours have the least amount of impact.
Now that you found the window, you still want to check if somebody is using our APEX apps.
So before I start an upgrade, I always check the last activity in our APEX apps.
The script I use to monitor the activity in our entire APEX environment - as SYS user run in SQL>
SELECT
workspace_name,
apex_session_id,
user_name,
remote_addr,
TO_CHAR(session_created, 'DD-MON-YYYY HH24:MI') AS session_created,
TO_CHAR(session_idle_timeout_on, 'DD-MON-YYYY HH24:MI') AS session_idle_timeout_on,
TO_CHAR(session_idle_timeout_on-(session_max_idle_sec/24/60/60), 'DD-MON-YYYY HH24:MI') AS last_activity,
TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI') AS time_now,
round((sysdate-(session_idle_timeout_on-(session_max_idle_sec/24/60/60)))*24*60) as minutes_ago
FROM apex_workspace_sessions
WHERE user_name NOT IN ('APEX_PUBLIC_USER','nobody')
ORDER BY minutes_ago, workspace_name, session_idle_timeout_on DESC;
This gives me the following overview:
So I see how many minutes ago (last column) which APEX workspace was used by which user. I could even follow the APEX session to find out more about the APP etc. but that is not really relevant for me. In the script, I only check for logged in users.
In case I also want to see for direct connections to the database, or I want more real-time info from the APEX session, I run the following script to check the session in the Oracle Database:
SELECT
sid,
serial#,
username,
osuser,
machine,
program,
--sql_id, sql_exec_start, event,
logon_time,
client_info AS workspace_id_auth_user,
module AS db_schema_apex_app_id_page_id,
action AS action,
client_identifier AS auth_user_apex_session
FROM gv$session
WHERE type = 'USER'
AND service_name = 'apex_pdb';
This gives me the following overview:
In the above screen you see I'm connected to the database with SQL Developer. The other sessions are coming from our connection pool. What is interesting is that APEX is instrumented to populate session info in the database like module, client_info, and client_identifier. This is important as APEX/ORDS is working with a connection pool so it would be hard to see what APEX session corresponded to which database session in case they didn't.
The above helps me to find a good time to perform upgrades, without impacting too many people.
So I thought to share my scripts as it might help you too.
become
had The question is always, when is a good time to upgrade?
eva
As we are hosting multiple applications and sites in our environment, we want to find a spot with minimal impact to our customers. For some applications which are only accessed by some people in Belgium, it's very easy to schedule an upgrade at night as the chances are not high people accessing the app. But in the case of APEX Office Print, we have customers all over the world in different timezones. So they might want to look at their dashboard or download the latest version at any moment in time.
For our AOP service itself, we have an internal policy we don't allow downtime. We load balance, so even when performing upgrades of AOP, there will always be a server available to handle the requests. For our Oracle database and APEX environment, we are fine with a few minutes downtime while upgrading the server.
In Oracle APEX itself, when you go to Monitor Activity - Page Views by User by Hour you get a graphical overview when your applications are being used:
This screen works well for a given workspace. If you want to look cross workspaces, for your entire APEX instance, you could build something yourself similar to the above. An overview like that would give you an idea based on historical usage which hours have the least amount of impact.
Now that you found the window, you still want to check if somebody is using our APEX apps.
So before I start an upgrade, I always check the last activity in our APEX apps.
The script I use to monitor the activity in our entire APEX environment - as SYS user run in SQL>
SELECT
workspace_name,
apex_session_id,
user_name,
remote_addr,
TO_CHAR(session_created, 'DD-MON-YYYY HH24:MI') AS session_created,
TO_CHAR(session_idle_timeout_on, 'DD-MON-YYYY HH24:MI') AS session_idle_timeout_on,
TO_CHAR(session_idle_timeout_on-(session_max_idle_sec/24/60/60), 'DD-MON-YYYY HH24:MI') AS last_activity,
TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI') AS time_now,
round((sysdate-(session_idle_timeout_on-(session_max_idle_sec/24/60/60)))*24*60) as minutes_ago
FROM apex_workspace_sessions
WHERE user_name NOT IN ('APEX_PUBLIC_USER','nobody')
ORDER BY minutes_ago, workspace_name, session_idle_timeout_on DESC;
This gives me the following overview:
So I see how many minutes ago (last column) which APEX workspace was used by which user. I could even follow the APEX session to find out more about the APP etc. but that is not really relevant for me. In the script, I only check for logged in users.
In case I also want to see for direct connections to the database, or I want more real-time info from the APEX session, I run the following script to check the session in the Oracle Database:
SELECT
sid,
serial#,
username,
osuser,
machine,
program,
--sql_id, sql_exec_start, event,
logon_time,
client_info AS workspace_id_auth_user,
module AS db_schema_apex_app_id_page_id,
action AS action,
client_identifier AS auth_user_apex_session
FROM gv$session
WHERE type = 'USER'
AND service_name = 'apex_pdb';
This gives me the following overview:
In the above screen you see I'm connected to the database with SQL Developer. The other sessions are coming from our connection pool. What is interesting is that APEX is instrumented to populate session info in the database like module, client_info, and client_identifier. This is important as APEX/ORDS is working with a connection pool so it would be hard to see what APEX session corresponded to which database session in case they didn't.
The above helps me to find a good time to perform upgrades, without impacting too many people.
So I thought to share my scripts as it might help you too.
Where are my static application files of Oracle APEX?
Ever got the error that there's an issue with your environment because the Application Express files have not been loaded when you try to open Oracle APEX in your environment?
Since the Announcement of the availability of Oracle APEX Static Resources on a Content Delivery Network I typically configure APEX to use the CDN.
Steps to do so for the entire environment:
Below is how I found out how the APEX images directory was configured and if it was using the CDN or local files.
Connect as SYS in a SQL window (SQLcl, SQL Developer, SQL Plus, ...):
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(apex_180100.wwv_flow_image_prefix.g_image_prefix);
3 end;
4 /
/i/
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_output.put_line(apex_180200.wwv_flow_image_prefix.g_image_prefix);
3 end;
4 /
https://static.oracle.com/cdn/apex/18.2.0.00.12/
PL/SQL procedure successfully completed.
Above you see that in APEX 18.1 the /i/ directory is used on the local web server.
On APEX 18.2 the CDN is being used.
Note: whenever you run the above command change to the correct APEX user (version), so for APEX 19.1 you use apex_190100.
Since the Announcement of the availability of Oracle APEX Static Resources on a Content Delivery Network I typically configure APEX to use the CDN.
Steps to do so for the entire environment:
- Navigate to the apex/utilities subdirectory from the directory you unzipped APEX to
- Connect to your database as the SYS user
- Run: @reset_image_prefix.sql
- when prompted for the image prefix, enter the correct path, e.g. https://static.oracle.com/cdn/apex/18.2.0.00.12/
Below is how I found out how the APEX images directory was configured and if it was using the CDN or local files.
Connect as SYS in a SQL window (SQLcl, SQL Developer, SQL Plus, ...):
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(apex_180100.wwv_flow_image_prefix.g_image_prefix);
3 end;
4 /
/i/
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_output.put_line(apex_180200.wwv_flow_image_prefix.g_image_prefix);
3 end;
4 /
https://static.oracle.com/cdn/apex/18.2.0.00.12/
PL/SQL procedure successfully completed.
Above you see that in APEX 18.1 the /i/ directory is used on the local web server.
On APEX 18.2 the CDN is being used.
Note: whenever you run the above command change to the correct APEX user (version), so for APEX 19.1 you use apex_190100.
Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People)
I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this. Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous […]
Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 9-12 July 2019 !!
I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Webinar will run between 9-12 July 2019 (6pm-10pm AEST): Webinar Series 9-12 July 2019 (start 6pm AEST, end 10pm AEST): This is a must attend seminar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning. […]
Oracle Database 19c Automatic Indexing: Predicted Back In 2008 (A Better Future)
I’ve recently received a number of correspondences regarding one of my most popular blog posts, dating back to February 2008: Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones). In the comments section, there’s an interesting discussion where I mention: “If Oracle19 does everything for you and all the various indexes structures get […]
Announcement: “Oracle Performance Diagnostics and Tuning” Seminar – Australia/NZ Winter Dates
I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Seminar throughout Australia and New Zealand this coming winter 2019. (See my Oracle Performance Diagnostics and Tuning Seminar page for all the seminar content and details). This is a must attend seminar aimed at Oracle professionals (both DBAs […]
Including an image from APEX Static Application Files in an email
Some people use APEX Office Print (AOP) to send out HTML emails based on a custom HTML template they created. Today we got a question in our AOP support inbox how to include images in those emails.
In an AOP Template, you can use a tag {%image} and AOP will replace that tag with a base64 encoded image. This works great when you open the resulting HTML file in a browser, but when using emails it's not so great as some email clients don't support base64 encoded images e.g. Outlook.
That the images don't always work is not really an AOP issue, it's more an issue with HTML emails.
Here's a good read on supporting images in HTML emails.
In short, it's better to include an URL to the image as that is supported by most, if not all, email clients. Typically the images are stored in a BLOB column in a table or people want to reference an image that they uploaded in APEX > Shared Components > Static Application Files.
This blog post shows a technique to reference an image stored in the database through a URL.
1. Create a new Public Page in an Oracle APEX Application e.g. Page 22
2. Create a region on the page
3. Create a hidden item on the page e.g. P22_FILE_NAME
4. Create a Before header Process "Retrieve image from DB" with following PL/SQL Code:
declare
l_file_name apex_application_static_files.file_name%type;
l_mime_type apex_application_static_files.mime_type%type;
l_file_charset apex_application_static_files.file_charset%type;
l_file_content apex_application_static_files.file_content%type;
begin
select nvl(file_name, 'output') as file_name,
nvl(mime_type, 'application/octet-stream') as mime_type,
nvl(file_charset, 'utf-8') as file_charset,
file_content
into l_file_name,
l_mime_type,
l_file_charset,
l_file_content
from apex_application_static_files
where application_id = :APP_ID
and file_name = :P22_FILE_NAME;
sys.htp.flush;
sys.htp.init;
owa_util.mime_header(l_mime_type, false);
sys.htp.p('Content-length:'||dbms_lob.getlength(l_file_content));
sys.htp.p('Content-Disposition:attachment; filename="'||l_file_name||'"; '||'filename*='||l_file_charset||''''''||l_file_name);
owa_util.http_header_close;
wpg_docload.download_file(l_file_content);
apex_application.stop_apex_engine;
exception
when no_data_found
then
sys.htp.p('');
apex_application.stop_apex_engine;
end;
5. That's it.
Now you can reference the image with the following URL: https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png
If you want to test the functionality in an email, go to SQL Workshop >SQL Commands and use the following code:
begin
apex_mail.send(
p_to => 'my@email.com',
p_from => 'my@email.com',
p_subj => 'Mail from APEX with inline image',
p_body => 'Please review the email in html.',
p_body_html => 'Here the inline image: <br/> <img alt="Image" height="150" width="150" src="https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png">');
apex_mail.push_queue;
end;
Now you can send HTML emails in APEX with references to images that are stored in the database.
In an AOP Template, you can use a tag {%image} and AOP will replace that tag with a base64 encoded image. This works great when you open the resulting HTML file in a browser, but when using emails it's not so great as some email clients don't support base64 encoded images e.g. Outlook.
That the images don't always work is not really an AOP issue, it's more an issue with HTML emails.
Here's a good read on supporting images in HTML emails.
In short, it's better to include an URL to the image as that is supported by most, if not all, email clients. Typically the images are stored in a BLOB column in a table or people want to reference an image that they uploaded in APEX > Shared Components > Static Application Files.
This blog post shows a technique to reference an image stored in the database through a URL.
1. Create a new Public Page in an Oracle APEX Application e.g. Page 22
2. Create a region on the page
3. Create a hidden item on the page e.g. P22_FILE_NAME
4. Create a Before header Process "Retrieve image from DB" with following PL/SQL Code:
declare
l_file_name apex_application_static_files.file_name%type;
l_mime_type apex_application_static_files.mime_type%type;
l_file_charset apex_application_static_files.file_charset%type;
l_file_content apex_application_static_files.file_content%type;
begin
select nvl(file_name, 'output') as file_name,
nvl(mime_type, 'application/octet-stream') as mime_type,
nvl(file_charset, 'utf-8') as file_charset,
file_content
into l_file_name,
l_mime_type,
l_file_charset,
l_file_content
from apex_application_static_files
where application_id = :APP_ID
and file_name = :P22_FILE_NAME;
sys.htp.flush;
sys.htp.init;
owa_util.mime_header(l_mime_type, false);
sys.htp.p('Content-length:'||dbms_lob.getlength(l_file_content));
sys.htp.p('Content-Disposition:attachment; filename="'||l_file_name||'"; '||'filename*='||l_file_charset||''''''||l_file_name);
owa_util.http_header_close;
wpg_docload.download_file(l_file_content);
apex_application.stop_apex_engine;
exception
when no_data_found
then
sys.htp.p('');
apex_application.stop_apex_engine;
end;
5. That's it.
Now you can reference the image with the following URL: https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png
If you want to test the functionality in an email, go to SQL Workshop >SQL Commands and use the following code:
begin
apex_mail.send(
p_to => 'my@email.com',
p_from => 'my@email.com',
p_subj => 'Mail from APEX with inline image',
p_body => 'Please review the email in html.',
p_body_html => 'Here the inline image: <br/> <img alt="Image" height="150" width="150" src="https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png">');
apex_mail.push_queue;
end;
Now you can send HTML emails in APEX with references to images that are stored in the database.
Unique Indexes Force Hints To Be “Ignored” Part II (One Of The Few)
In Part I, I showed a demo of how the introduction of a Unique Index appears to force a hint to be “ignored”. This is a classic case of what difference a Unique Index can make in the CBO deliberations. So what’s going on here? When I run the first, un-hinted query: we notice something a […]
Region only shown in development mode in Oracle APEX
In the last months, I had to look up several times in different projects how to show a region only when I was logged in into the App Builder in Oracle Application Express (APEX). So I thought to write a quick post on it.
In one project we had to log in with different users to test the behavior of the authorization schemes, so people saw the things they should see and could do the things they are allowed to do. As the logins where not straight forward we created a region with those test credentials. Other people were testing too with their own credentials, so we really wanted to keep the original login page, and decided to just add a region on the page we would only see when we were logged in into APEX itself.
Today I added some new pages to an app and wanted to make sure the navigation to those pages were only visible to me. I know, you should do this in DEV, and then when all is fine, propagate the app to TEST and PROD. The truth is, I've some applications that only exist in one environment and so I update straight in "production". Those apps are still backed up automatically every night, so worst case I can always take the version of the previous day. But just to be clear, this is not good practice ;)
So how do you show a region only when you are in development mode in Oracle APEX?
You go to the Conditions section of your region, list entry or any component in APEX really, and add a PL/SQL Expression: apex_application.g_edit_cookie_session_id is not null
Alternative you can use "Item / Column not null" and specify APP_BUILDER_SESSION:
*) updated blog post 11-FEB-2019 after comment of Christian (thanks Christian!)
It would be cool if there was a condition type "Development Mode", but maybe I'm the only one needing this...
Typically you would use Build Options (see Shared Components) to include or exclude certain functionality in your APEX app, but in the above two use cases, it won't really work.
Another enhancement would be that the Status of the Build option would include "Dev Only" next to Include and Exclude.
In one project we had to log in with different users to test the behavior of the authorization schemes, so people saw the things they should see and could do the things they are allowed to do. As the logins where not straight forward we created a region with those test credentials. Other people were testing too with their own credentials, so we really wanted to keep the original login page, and decided to just add a region on the page we would only see when we were logged in into APEX itself.
Today I added some new pages to an app and wanted to make sure the navigation to those pages were only visible to me. I know, you should do this in DEV, and then when all is fine, propagate the app to TEST and PROD. The truth is, I've some applications that only exist in one environment and so I update straight in "production". Those apps are still backed up automatically every night, so worst case I can always take the version of the previous day. But just to be clear, this is not good practice ;)
So how do you show a region only when you are in development mode in Oracle APEX?
You go to the Conditions section of your region, list entry or any component in APEX really, and add a PL/SQL Expression: apex_application.g_edit_cookie_session_id is not null
Alternative you can use "Item / Column not null" and specify APP_BUILDER_SESSION:
*) updated blog post 11-FEB-2019 after comment of Christian (thanks Christian!)
It would be cool if there was a condition type "Development Mode", but maybe I'm the only one needing this...
Typically you would use Build Options (see Shared Components) to include or exclude certain functionality in your APEX app, but in the above two use cases, it won't really work.
Another enhancement would be that the Status of the Build option would include "Dev Only" next to Include and Exclude.
“Oracle Indexing Internals and Best Practices” Seminar – Berlin 8-9 May: DOAG Website
Just a short note to say that DOAG have now a registration page for my upcoming “Oracle Indexing Internals and Best Practices” seminar running in Berlin, Germany on 8-9 May 2019. For all the details regarding this acclaimed educational experience and how to book your place, please visit: https://www.doag.org/de/eventdetails?tx_doagevents_single[id]=577320 Please mention you heard this seminar […]
Unique Indexes Force Hints To Be “Ignored” Part I (What’s Really Happening)
As I was compiling my new “Oracle Diagnostics and Performance Tuning” seminar, I realised there were quite a number of indexing performance issues I haven’t discussed here previously. The following is a classic example of what difference a Unique Index can have over a Non-Unique index, while also covering the classic myth that Oracle sometimes […]
Announcement: “Oracle Indexing Internals and Best Practices Seminar” – Berlin 8-9 May 2019 !!
I’m very excited to announce I’ll be running my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar in conjunction with the German Oracle User Group (DOAG) in Berlin, Germany on 8-9 May 2019. The venue will be the DOAG Offices in Berlin (DOAG Office, 6th Floor, Tempelhofer Weg 64, 12347 Berlin). Because of venue […]
The best free database... Google is wrong!
When you search on Google for "the best free database", the below is what you get (search done 11-NOV-2018 and again on 12-JAN-2019 and 28-JAN-2019). To my surprise, there's no Oracle on the list? The reason is Google took the answer from this review. As I don't want people to see this screenshot, I put in red what is wrong with the answer, so in case Google shows images, and people don't read this blog post, they don't get the wrong answer ;)
The above is so wrong for me, the best free database in the world is Oracle Express Edition (XE). Oracle released XE18c on October 19th, 2018. This database is unbelievable. You basically get an Enterprise Edition version and almost all options are turned on! It's amazing, the only restrictions you have is on the amount of RAM (2GB) and disk space (12GB). You even have the pluggable database architecture and can create 3 PDBs (pluggable databases).
In my opinion, there is no other free database in the world that will beat this. Below I will go in more detail why I like this Oracle XE18c so much, but first, let me show you Google actually knows the right answer too.
Google says "People also ask": "What are the top 5 databases available on the market?" and here Oracle is number one. The other question is "What is the best database software for small businesses?" Oracle number one again. If the question would be "What is the best database software for enterprise businesses?" Oracle is number one too, this is common knowledge.
Google's algorithm to answer the first question, just got it wrong. How can Oracle be number one and be the best, but not in the free section, whereas their best database is available for free? :)
Google allows you to comment on their search results, which I did:
Why do I like Oracle XE 18c so much?
When we talk about Oracle XE, we really talk about the full Oracle database in general. Yes, there are a couple of limitations, but nevertheless, you get the full feature set of the Oracle database! All the good stuff why Oracle shines is there: to have the best performance you can use partitioning and online index rebuilds (and in the future automatic index creation!), to increase high availability you have the full flashback technology to your disposal, for security Oracle has VPD, Real Application Security, Database Vault... Oracle plans to release a new version of XE every year too, so you have always the latest and greatest.
I should write another blog post why I like the Oracle database so much, but I encourage you to just try it and decide yourself.
Getting started with Oracle XE
If you just want to try Oracle XE, most likely the easiest way without hitting your system, is to go with the Oracle docker container. Here're the steps to get Oracle XE running in an Oracle docker container.
If you don't have Docker and Git yet, download and install first.
Open a Terminal or Command Prompt and run following commands:
git clone https://github.com/oracle/docker-images.git
cd docker-images/OracleDatabase/SingleInstance/dockerfiles
Download Oracle XE
Copy the oracle-database-xe-18c-1.0-1.x86_64.rpm in the docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 directory
Move on by running following command:
./buildDockerImage.sh -v 18.4.0 -x -i
docker images
docker run --name OracleXE -p 1521:1521 -p 8080:8080 -e ORACLE_SID=XE -e ORACLE_PWD=oracle -v /Users/dgielis/git/docker-images/OracleDatabase/SingleInstance/dockerfiles:/opt/oracle/oradata oracle/database:18.4.0-xe
...
And voila, you are done! (more info on the Oracle Docker images)
To get a view in your database you can use Oracle SQL Developer. Here's how you connect to it:
The next thing would be to install Oracle APEX, so you don't only have the best database in the world, but also the best low code platform in the world, which works absolutely awesome with the Oracle database.
The above is so wrong for me, the best free database in the world is Oracle Express Edition (XE). Oracle released XE18c on October 19th, 2018. This database is unbelievable. You basically get an Enterprise Edition version and almost all options are turned on! It's amazing, the only restrictions you have is on the amount of RAM (2GB) and disk space (12GB). You even have the pluggable database architecture and can create 3 PDBs (pluggable databases).
In my opinion, there is no other free database in the world that will beat this. Below I will go in more detail why I like this Oracle XE18c so much, but first, let me show you Google actually knows the right answer too.
Google says "People also ask": "What are the top 5 databases available on the market?" and here Oracle is number one. The other question is "What is the best database software for small businesses?" Oracle number one again. If the question would be "What is the best database software for enterprise businesses?" Oracle is number one too, this is common knowledge.
Google's algorithm to answer the first question, just got it wrong. How can Oracle be number one and be the best, but not in the free section, whereas their best database is available for free? :)
Google allows you to comment on their search results, which I did:
Why do I like Oracle XE 18c so much?
When we talk about Oracle XE, we really talk about the full Oracle database in general. Yes, there are a couple of limitations, but nevertheless, you get the full feature set of the Oracle database! All the good stuff why Oracle shines is there: to have the best performance you can use partitioning and online index rebuilds (and in the future automatic index creation!), to increase high availability you have the full flashback technology to your disposal, for security Oracle has VPD, Real Application Security, Database Vault... Oracle plans to release a new version of XE every year too, so you have always the latest and greatest.
I should write another blog post why I like the Oracle database so much, but I encourage you to just try it and decide yourself.
Getting started with Oracle XE
If you just want to try Oracle XE, most likely the easiest way without hitting your system, is to go with the Oracle docker container. Here're the steps to get Oracle XE running in an Oracle docker container.
If you don't have Docker and Git yet, download and install first.
Open a Terminal or Command Prompt and run following commands:
git clone https://github.com/oracle/docker-images.git
cd docker-images/OracleDatabase/SingleInstance/dockerfiles
Download Oracle XE
Copy the oracle-database-xe-18c-1.0-1.x86_64.rpm in the docker-images/OracleDatabase/SingleInstance/dockerfiles/18.4.0 directory
Move on by running following command:
./buildDockerImage.sh -v 18.4.0 -x -i
docker images
docker run --name OracleXE -p 1521:1521 -p 8080:8080 -e ORACLE_SID=XE -e ORACLE_PWD=oracle -v /Users/dgielis/git/docker-images/OracleDatabase/SingleInstance/dockerfiles:/opt/oracle/oradata oracle/database:18.4.0-xe
...
And voila, you are done! (more info on the Oracle Docker images)
To get a view in your database you can use Oracle SQL Developer. Here's how you connect to it:
The next thing would be to install Oracle APEX, so you don't only have the best database in the world, but also the best low code platform in the world, which works absolutely awesome with the Oracle database.
Trying to decide which APEX events to attend? Here's where I'll be.
I love going to conferences, to catch up with friends, be part of the vibrant Oracle APEX community, gain and share knowledge and of course demo APEX Office Print (AOP).
Conferences are a great way to get some new ideas and insights. You learn not only from the content but even more from the different cultures and background of people and the way they approach things.
My schedule till June looks like this:
ITOUG Tech Day Milano, Italy: 30-JAN-2019
This day is part of the ITOUG Tech Days. I've never presented in Italy, so I really look forward to this first time. I'll present on "Bringing your Oracle Database alive with APEX".
APEX Meetup Düsseldorf, Germany: 5-FEB-2019
As we did some major releases with AOP 18.1 and 19.1 and we have many customers in Germany, Niels asked to present on Reporting in APEX. As it's a meetup, we have more room to make it really interactive, so if you are in the region of Düsseldorf and Ratingen, stop by and ask me any question. I'll show some cool tricks of AOP and will give some insight what's coming later in the year.
APEX World, the Netherlands: 25/26-MAR-2019
I don't think I've ever missed a version of APEX World. It became the biggest APEX only conference in the world, and it's basically in my backyard :) This year is special too, as it's the 10th year anniversary! My company, APEX R&D, has been a long time sponsor of the event. This is the only conference where we also have a booth, so you can stop by any time you want and ask us many questions :) At our booth, we show the latest and greatest dev build of APEX Office Print (AOP)!
I'll give my "Augmented Reality & Virtual Reality with APEX" presentation this year.
APEX Alpe Aldria, Croatia: 12-APR-2019
Last year this conference got its first edition and it was a great success. My friends Peter, Aljaz, and Dario really know how to make a great conference. Last year AOP sponsored and Sunil, our lead backend architect of AOP, was there to answer any questions. This year most of our team is going. I'll give a presentation "APEX Reporting Tips & Tricks", similar to the webinar I did for AUSOUG.
APEX Connect, Germany: 7/9-MAY-2019
The last couple of years I've presented at this conference. APEX is being used a lot in Germany, so it's great to see so many use cases of APEX. This year I was asked to do my "Augmented Reality & Virtual Reality with APEX" presentation. In our office we have 3 VR systems running (with HTC Vive) and are doing some research on cool use cases. I dream one day we can develop our APEX apps like Tom Cruise did in the movie Minority Report :)
ODTUG KScope, Seattle, US: 23/27-JUN-2019
Since my first attendance in 2006, this is my favorite conference. It's the go-to place to meet the APEX development team, and so many smart people you meet daily on the internet.
It's unsure yet what I'll present on this year, reviews are still ongoing, but for sure there are many good presentations there and the latest and greatest of the APEX Dev Team. If you can ever go to this conference, I would definitely recommend it!
If you are at one of the above events and you want to chat, don't hesitate to stop me. I would love to meet you. I'm thankful you read my blog and I would love to hear more about you.
Conferences are a great way to get some new ideas and insights. You learn not only from the content but even more from the different cultures and background of people and the way they approach things.
My schedule till June looks like this:
ITOUG Tech Day Milano, Italy: 30-JAN-2019
This day is part of the ITOUG Tech Days. I've never presented in Italy, so I really look forward to this first time. I'll present on "Bringing your Oracle Database alive with APEX".
APEX Meetup Düsseldorf, Germany: 5-FEB-2019
As we did some major releases with AOP 18.1 and 19.1 and we have many customers in Germany, Niels asked to present on Reporting in APEX. As it's a meetup, we have more room to make it really interactive, so if you are in the region of Düsseldorf and Ratingen, stop by and ask me any question. I'll show some cool tricks of AOP and will give some insight what's coming later in the year.
APEX World, the Netherlands: 25/26-MAR-2019
I don't think I've ever missed a version of APEX World. It became the biggest APEX only conference in the world, and it's basically in my backyard :) This year is special too, as it's the 10th year anniversary! My company, APEX R&D, has been a long time sponsor of the event. This is the only conference where we also have a booth, so you can stop by any time you want and ask us many questions :) At our booth, we show the latest and greatest dev build of APEX Office Print (AOP)!
I'll give my "Augmented Reality & Virtual Reality with APEX" presentation this year.
APEX Alpe Aldria, Croatia: 12-APR-2019
Last year this conference got its first edition and it was a great success. My friends Peter, Aljaz, and Dario really know how to make a great conference. Last year AOP sponsored and Sunil, our lead backend architect of AOP, was there to answer any questions. This year most of our team is going. I'll give a presentation "APEX Reporting Tips & Tricks", similar to the webinar I did for AUSOUG.
APEX Connect, Germany: 7/9-MAY-2019
The last couple of years I've presented at this conference. APEX is being used a lot in Germany, so it's great to see so many use cases of APEX. This year I was asked to do my "Augmented Reality & Virtual Reality with APEX" presentation. In our office we have 3 VR systems running (with HTC Vive) and are doing some research on cool use cases. I dream one day we can develop our APEX apps like Tom Cruise did in the movie Minority Report :)
ODTUG KScope, Seattle, US: 23/27-JUN-2019
Since my first attendance in 2006, this is my favorite conference. It's the go-to place to meet the APEX development team, and so many smart people you meet daily on the internet.
It's unsure yet what I'll present on this year, reviews are still ongoing, but for sure there are many good presentations there and the latest and greatest of the APEX Dev Team. If you can ever go to this conference, I would definitely recommend it!
If you are at one of the above events and you want to chat, don't hesitate to stop me. I would love to meet you. I'm thankful you read my blog and I would love to hear more about you.
APEX Office Print (AOP) 19.1: Printing and Exporting made easy in Oracle APEX
To start the new year in an awesome way, we released APEX Office Print (AOP) 19.1 on January 11th. I typically don't blog about every new AOP release, but this is an important release in our history of the product.
Our dream is that every developer can use AOP, that is why in AOP 18.1 (released September 2018) we made a Free Cloud Tier.
With AOP 19.1 we go one step further... from now on you can run AOP in Developer Mode, which means you can call our cloud as much as you like in this mode. Your number of reports/credits in your plan are not touched at all. When you want to test AOP or are developing some new reports, you can use development mode so you don't need to use your credits for that.
We had been thinking about this setting for some time, but Jon from JMJ Cloud reminded us how important it is for you in his nice blog post: APEX Office Print – Is it the APEX printing solution we have all been waiting for?
Just like in previous versions we added a number of new features, fixed some bugs and made several enhancements. You can view the release notes for every version here.
AOP 19.1 is again a free update for all our users, Cloud and On-Premises (with a valid maintenance contract).
We love to listen to our customers and help them as good as we can to make their projects a success. That is why we like to release frequently, so creating the reports you want is easy, fast and integrated with Oracle APEX.
In this post, I want to highlight some of the APEX Office Print (AOP) features that I believe make a difference, and why AOP became the go-to solution when you want to print and export your data in Oracle APEX. If you want to see AOP in action, definitely check out the AOP Sample App.
Architecture
Just like BI Publisher or XSL-FO, AOP has a server component. The difference is that AOP is one executable, has a built-in web server which can autoscale depending the number of CPU cores and it listens on incoming requests with a JSON payload. The AOP Server is very simple to install, upgrade and maintain, yet flexible and scalable out-of-the-box.
To make it really easy to do calls to the AOP Server, we built a PL/SQL API on top of the REST interface. As we love Oracle APEX so much, we built an APEX Plug-in on top of the PL/SQL API.
Our idea is that you are up-and-running in less than 5 minutes.
Integration with Oracle APEX
AOP is the most integrated solution for APEX apps on the market. We went much further than the built-in BI Publisher and XSL-FO support. AOP understands your Classic Reports, Interactive Reports, and Grids, Charts and Calendars. AOP lets you print or export your Interactive Report and Grid exactly as you see it on the screen, with highlights, computations, breaks, group by etc.
In the APEX Plug-in you specify the static id of the region(s) and AOP will do the rest.
The APEX Plug-in is really flexible and the easiest way to get your reports done in no time. You specify your template, the data source(s) and your output, that's it.
Based on Templates
AOP is template driven, you basically create a template in your favorite editor. AOP supports templates in Word, Excel, Powerpoint, PDF, HTML, Markdown, Text and CSV.
In the template, you use specific tags AOP understands. You find an overview of tags in the AOP documentation.
PDF Tools
As many people generate PDF documents, since AOP 18 we are focussing to include more and more PDF-specific features. AOP allows you to generate PDFs from almost any file format, even images, you can merge PDFs, split them, password protect the PDF and you can even include a watermark.
We also have a special PDF, called Single Page PDF. Some people don't know the size of the document, so they just want a PDF with a variable size. For example when you get a receipt in a restaurant, depending on the number of dishes and drinks the PDF is different in size.
One more thing... AOP Web Editor
We are developers ourselves and have been using AOP since 2015. Sometimes you don't know what's going on, so from day one we heavily instrumented AOP to see exactly what it's doing and give you all the tools to make debugging fun. The Web Editor allows you to create documents really fast, look at debug info, see the different requests and try different options really fast.
Support
We love our customers and find support extremely important as we want you to be successful. You can contact us through our support email or through the new AOP Slack Channel on apex.world.
Final words
The above is just a small set of features I believe make a huge difference compared to any other engine. You don't have to take my word for it, just give AOP a try with our free version and development mode and decide yourself :) And know you are not alone, we are proud of every single customer we have from large customers like Siemens, NASA and even Oracle, to smaller customers like Storm-Petrel (who use AOP extensively!).
Our dream is that every developer can use AOP, that is why in AOP 18.1 (released September 2018) we made a Free Cloud Tier.
With AOP 19.1 we go one step further... from now on you can run AOP in Developer Mode, which means you can call our cloud as much as you like in this mode. Your number of reports/credits in your plan are not touched at all. When you want to test AOP or are developing some new reports, you can use development mode so you don't need to use your credits for that.
We had been thinking about this setting for some time, but Jon from JMJ Cloud reminded us how important it is for you in his nice blog post: APEX Office Print – Is it the APEX printing solution we have all been waiting for?
Just like in previous versions we added a number of new features, fixed some bugs and made several enhancements. You can view the release notes for every version here.
AOP 19.1 is again a free update for all our users, Cloud and On-Premises (with a valid maintenance contract).
We love to listen to our customers and help them as good as we can to make their projects a success. That is why we like to release frequently, so creating the reports you want is easy, fast and integrated with Oracle APEX.
In this post, I want to highlight some of the APEX Office Print (AOP) features that I believe make a difference, and why AOP became the go-to solution when you want to print and export your data in Oracle APEX. If you want to see AOP in action, definitely check out the AOP Sample App.
Architecture
Just like BI Publisher or XSL-FO, AOP has a server component. The difference is that AOP is one executable, has a built-in web server which can autoscale depending the number of CPU cores and it listens on incoming requests with a JSON payload. The AOP Server is very simple to install, upgrade and maintain, yet flexible and scalable out-of-the-box.
To make it really easy to do calls to the AOP Server, we built a PL/SQL API on top of the REST interface. As we love Oracle APEX so much, we built an APEX Plug-in on top of the PL/SQL API.
Our idea is that you are up-and-running in less than 5 minutes.
Integration with Oracle APEX
AOP is the most integrated solution for APEX apps on the market. We went much further than the built-in BI Publisher and XSL-FO support. AOP understands your Classic Reports, Interactive Reports, and Grids, Charts and Calendars. AOP lets you print or export your Interactive Report and Grid exactly as you see it on the screen, with highlights, computations, breaks, group by etc.
In the APEX Plug-in you specify the static id of the region(s) and AOP will do the rest.
The APEX Plug-in is really flexible and the easiest way to get your reports done in no time. You specify your template, the data source(s) and your output, that's it.
Based on Templates
AOP is template driven, you basically create a template in your favorite editor. AOP supports templates in Word, Excel, Powerpoint, PDF, HTML, Markdown, Text and CSV.
In the template, you use specific tags AOP understands. You find an overview of tags in the AOP documentation.
PDF Tools
As many people generate PDF documents, since AOP 18 we are focussing to include more and more PDF-specific features. AOP allows you to generate PDFs from almost any file format, even images, you can merge PDFs, split them, password protect the PDF and you can even include a watermark.
We also have a special PDF, called Single Page PDF. Some people don't know the size of the document, so they just want a PDF with a variable size. For example when you get a receipt in a restaurant, depending on the number of dishes and drinks the PDF is different in size.
One more thing... AOP Web Editor
We are developers ourselves and have been using AOP since 2015. Sometimes you don't know what's going on, so from day one we heavily instrumented AOP to see exactly what it's doing and give you all the tools to make debugging fun. The Web Editor allows you to create documents really fast, look at debug info, see the different requests and try different options really fast.
Support
We love our customers and find support extremely important as we want you to be successful. You can contact us through our support email or through the new AOP Slack Channel on apex.world.
Final words
The above is just a small set of features I believe make a huge difference compared to any other engine. You don't have to take my word for it, just give AOP a try with our free version and development mode and decide yourself :) And know you are not alone, we are proud of every single customer we have from large customers like Siemens, NASA and even Oracle, to smaller customers like Storm-Petrel (who use AOP extensively!).
Upgrading of Oracle APEX 18.x and future
Late September 2018 Oracle Application Express (APEX) 18.2 was released.
Since 2018, Oracle adopts a new versioning for their software, and APEX is following that. The plan of the Oracle APEX development team is to do two releases a year which carry the year and the release number of that year e.g. 18.1, 18.2, 19.1.
I like this new way of providing us with new releases. You don't know the exact timing of the releases, but you know the development team will be close to release version 1 in March/April and version 2 in September/October timeframe. In between you can download one-of patches or patch set releases through Oracle Support.
However there's one important change compared to before you have to be aware of:
every new version of Oracle APEX will be a complete install, with its own schema.
The biggest benefit of this approach is that the downtime to upgrade an Oracle APEX instance can be very minimal and the install is fast. You can read more about it in Maximizing Uptime During an Application Express Upgrade. I believe the Oracle Cloud is a big driver for this, but it benefits us all.
Another benefit is that if you can download and install all APEX releases in Oracle XE more easily. Before if you had installed APEX 5.1.0 and wanted to upgrade to APEX 5.1.2, 5.1.3, 5.1.4 you had to download the software from Oracle Support. But you could only do that with a valid support contract. If you wanted a complete free system with Oracle XE, you could download the latest version of APEX and do a new install and migrate over the workspaces and apps. Since APEX 18 you can always install those versions and APEX itself is doing the migration for you.
Now the biggest disadvantage of this approach is that you can't easily export and import your apps and plug-ins to a different version. Before when you might have Oracle APEX 5.1.0 (released Dec. 2016) in one environment and Oracle APEX 5.1.4 (released Dec. 2017) in another (or anything in between) and you could easily export and import between those environments. Oracle APEX exports have always been compatible till the second dot, so you could export import from any 5.1.x to another 5.1.x. You can import an APEX 18.1 version in an APEX 18.2 environment, but not the other way.
We typically move fast to a new version of APEX as we have to support those versions for APEX Office Print. I also blogged how we upgrade major releases by running multiple PDBs. With APEX 5.0, released in April 2015 and APEX 18.1 released in May 2018, we covered over 3 years in 3 PDBs (1 PDB for every major release of APEX 5.0, 5.1, 18.1). With the new version numbers, with 3 PDBs we cover a maximum of 1.5 years.
If you are aware of this, you can plan your application releases with it. And these new release numbers will make customers probably want to upgrade faster, or at least once a year as numbers go up fast :) As developers it's not only nice to work with new technology, it's also more productive for everyone.
Oracle APEX is not the only software in our stack right? ORDS, SQL Developer, Oracle Database, they follow the new release numbers. ORDS released a few days ago 18.4. The ORDS/SQL Developer/SQLcl team releases every quarter and typically use the quarter of development in their release number.
Tim Hall wrote a nice article on his thoughts on the new release numbers for the Oracle Database.
I love getting new versions and play with the new features or not wait long on bug fixes. We release frequently with APEX Office Print (AOP) too, 10 days ago we released AOP 19.1, but that is for another blog post!
Happy upgrading :)
Since 2018, Oracle adopts a new versioning for their software, and APEX is following that. The plan of the Oracle APEX development team is to do two releases a year which carry the year and the release number of that year e.g. 18.1, 18.2, 19.1.
I like this new way of providing us with new releases. You don't know the exact timing of the releases, but you know the development team will be close to release version 1 in March/April and version 2 in September/October timeframe. In between you can download one-of patches or patch set releases through Oracle Support.
However there's one important change compared to before you have to be aware of:
every new version of Oracle APEX will be a complete install, with its own schema.
The biggest benefit of this approach is that the downtime to upgrade an Oracle APEX instance can be very minimal and the install is fast. You can read more about it in Maximizing Uptime During an Application Express Upgrade. I believe the Oracle Cloud is a big driver for this, but it benefits us all.
Another benefit is that if you can download and install all APEX releases in Oracle XE more easily. Before if you had installed APEX 5.1.0 and wanted to upgrade to APEX 5.1.2, 5.1.3, 5.1.4 you had to download the software from Oracle Support. But you could only do that with a valid support contract. If you wanted a complete free system with Oracle XE, you could download the latest version of APEX and do a new install and migrate over the workspaces and apps. Since APEX 18 you can always install those versions and APEX itself is doing the migration for you.
Now the biggest disadvantage of this approach is that you can't easily export and import your apps and plug-ins to a different version. Before when you might have Oracle APEX 5.1.0 (released Dec. 2016) in one environment and Oracle APEX 5.1.4 (released Dec. 2017) in another (or anything in between) and you could easily export and import between those environments. Oracle APEX exports have always been compatible till the second dot, so you could export import from any 5.1.x to another 5.1.x. You can import an APEX 18.1 version in an APEX 18.2 environment, but not the other way.
We typically move fast to a new version of APEX as we have to support those versions for APEX Office Print. I also blogged how we upgrade major releases by running multiple PDBs. With APEX 5.0, released in April 2015 and APEX 18.1 released in May 2018, we covered over 3 years in 3 PDBs (1 PDB for every major release of APEX 5.0, 5.1, 18.1). With the new version numbers, with 3 PDBs we cover a maximum of 1.5 years.
If you are aware of this, you can plan your application releases with it. And these new release numbers will make customers probably want to upgrade faster, or at least once a year as numbers go up fast :) As developers it's not only nice to work with new technology, it's also more productive for everyone.
Oracle APEX is not the only software in our stack right? ORDS, SQL Developer, Oracle Database, they follow the new release numbers. ORDS released a few days ago 18.4. The ORDS/SQL Developer/SQLcl team releases every quarter and typically use the quarter of development in their release number.
Tim Hall wrote a nice article on his thoughts on the new release numbers for the Oracle Database.
I love getting new versions and play with the new features or not wait long on bug fixes. We release frequently with APEX Office Print (AOP) too, 10 days ago we released AOP 19.1, but that is for another blog post!
Happy upgrading :)
Announcement: New “Oracle Diagnostics and Performance Tuning” Seminar Now Available !!
It’s been a work in progress for quite some time, with many of my customers asking when will it be completed. Well, I’m very excited to announce that I have finally completed my new 2 day “Oracle Diagnostics and Performance Tuning” seminar and that it’s ready to be presented. I already have a number of […]
New “Let’s Talk Database” events in Australia/NZ in February/March 2019 !!
Very excited to announce some new “Let’s Talk Database” events scheduled for February 2019 in various locations in Australia/NZ: Canberra: Wednesday, 20 February 2019: Registration Link Sydney: Thursday, 21 Feburary 2019: Registration Link Brisbane: Friday, 22 Feburary 2019: Registration Link Melbourne: Wednesday, 27 February 2019: Registration Link Wellington: Friday, 1 March 2019: Registration Link […]
