Development
Working with Batch REST Calls to Business Objects in Visual Builder
In a previous blog post I showed the basics of working with the REST interfaces to manipulate data in business objects in Visual Builder. There is one more technique that I wanted to cover, and that is for executing multiple operations on multiple records and objects with a single REST call - what we refer to as batch operation. See the "Making Batch Requests" section in our doc.
Using this approach can of course improve the performance of your application when you need to manipulate multiple records. For example, if you worked with an editable table and then wanted to save all the changed records with a single REST call - you could construct the required json structure and execute all the updates at once. The important things to watch for are the required Content-Type header application/vnd.oracle.adf.batch+json , the use of a POST operation, and the fact that you access the root URL for your service. Then of course keep an eye on constructing the JSON payload correctly.
See the demo below for an example of performing various CRUD operations in one go.
Monitoring your Oracle APEX environment; when is a good time to do an upgrade?
The question is always, when is a good time to upgrade?
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:
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.
Working with Multiple Row Selection Tables in Visual Builder
The Oracle JET table component allows you to select multiple records in one go using the regular ctrl & shift key combinations. But once the user selected rows, how do you know which rows were selected? How do you track this?
The video below shows you the basics. As the JET tag documentation will show you, the table has a selection property which is an array of the selected records. This selections array is passed to the selection event on the table that you can hook to in with an action chain in VBCS. The array has a row for each range of records you selected listing their keys and indexes in the table.
It's up to you to parse this information if you want to operate on these rows.
The code in the JavaScript method is:
PageModule.prototype.listSelection = function(selection) { console.log("we got " + selection.length + " selections") for (var i = 0; i < selection.length; i++) { console.log("start key " + selection[i].startKey.row + ", start index " + +selection[i].startIndex.row); console.log("end key " + selection[i].endKey.row + ", end index " + +selection[i].endIndex.row); } }
Where are my static application files of Oracle APEX?
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.
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.
Accessing Visual Builder Business Objects through REST Services
One nice thing about Visual Builder is that it helps you not only with the creation of the front end, but also with the creation of the backend for your app - through the use of the business objects feature. When you develop the UI in VB it is accessing the business objects through REST endpoints, and these REST endpoints can also be exposed so other UIs or systems can access the same data.
In the video below, I'm showing you how to access the business objects and perform the full set of CRUD operations against them.
There are many more capabilities to these REST APIs including advanced query options that help you optimize the amount of data you are getting back from them. Read about it in our documentation.
One more note - in the demo I use the Bearer Token security, but you can also switch to use basic authentication to access your services. To do this in your application settings, under the business object tab, you can check the check box for enabling this.
Including an image from APEX Static Application Files in an email
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.
Another bug with lateral
Compare the results of the following query with the clause “fetch first 2 rows only”
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
sys.ku$_objnumpair(1,1),
sys.ku$_objnumpair(1,2),
sys.ku$_objnumpair(1,3),
sys.ku$_objnumpair(3,1),
sys.ku$_objnumpair(3,2),
sys.ku$_objnumpair(3,3)
)))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
*
from t,
lateral(select t1.a,t2.b
from t1,t2
where t1.a = t2.a
and t1.a = t.id
order by t2.b
fetch first 2 rows only
)(+)
order by id;
ID A B
---------- ---------- ----------
1 1 1
1 3 1
2 1 1
2 3 1
3 1 1
3 3 1
4 1 1
4 3 1
5 1 1
5 3 1
6 1 1
6 3 1
7 1 1
7 3 1
14 rows selected.
with this one (i’ve just commented out the line with “fetch-first-rows-only”:
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
sys.ku$_objnumpair(1,1),
sys.ku$_objnumpair(1,2),
sys.ku$_objnumpair(1,3),
sys.ku$_objnumpair(3,1),
sys.ku$_objnumpair(3,2),
sys.ku$_objnumpair(3,3)
)))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
*
from t,
lateral(select t1.a,t2.b
from t1,t2
where t1.a = t2.a
and t1.a = t.id
order by t2.b
-- fetch first 2 rows only
)(+)
order by id;
ID A B
---------- ---------- ----------
1 1 2
1 1 3
1 1 1
2
3 3 2
3 3 1
3 3 3
4
5
6
7
11 rows selected.
Obviously, the first query should return less rows than second one, but we can see that it returned more rows and join predicate “and t1.a = t.id” was ignored, because A and B are not empty and “A” is not equal to t.ID.
Lateral view decorrelation(VW_DCL) causes wrong results with rownum
Everyone knows that rownum in inline views blocks many query transformations, for example pushing/pulling predicates, scalar subquery unnesting, etc, and many people use it for such purposes as a workaround to avoid unwanted transformations(or even CBO bugs).
If we pull the predicate “column_value = 3” from the following query to higher level
select *
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
and column_value = 3;
COLUMN_VALUE
------------
3
3
we will get different results:
select *
from (select *
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
)
where column_value = 3;
no rows selected
Doc ID 62340.1
But we recently encountered a bug with it: lateral view with ROWNUM returns wrong results in case of lateral view decorrelation.
Compare results of this query with and without no_decorrelation hint:
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
*
from t,
lateral(select/*+ no_decorrelate */ rownum rn
from t1,t2
where t1.a=t2.b and t1.a = t.id
)(+)
order by 1,2;
ID RN
---------- ----------
1 1
1 2
2
3 1
3 2
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
*
from t,
lateral(select rownum rn
from t1,t2
where t1.a=t2.b and t1.a = t.id
)(+)
order by 1,2;
ID RN
---------- ----------
1 1
1 2
2
3 3
3 4
Of course, we can draw conclusions even from these results: we can see that in case of decorrelation(query with hint) rownum was calculated before the join. But to be sure we can check optimizer’s trace 10053:
******* UNPARSED QUERY IS *******
SELECT VALUE(KOKBF$2) "ID", "VW_DCL_76980902"."RN" "RN"
FROM TABLE("ODCINUMBERLIST"(1, 2, 3)) "KOKBF$2",
(SELECT ROWNUM "RN_0", VALUE(KOKBF$0) "ITEM_3"
FROM TABLE("ODCINUMBERLIST"(1, 3)) "KOKBF$0",
TABLE("ODCINUMBERLIST"(1, 1, 3, 3)) "KOKBF$1"
WHERE VALUE(KOKBF$0) = VALUE(KOKBF$1)
) "VW_DCL_76980902"
WHERE "VW_DCL_76980902"."ITEM_3"(+) = VALUE(KOKBF$2)
ORDER BY VALUE(KOKBF$2), "VW_DCL_76980902"."RN"
*************************
I’ll modify it a bit just to make it more readable:
we can see that
select
*
from t,
lateral(select rownum rn
from t1,t2
where t1.a=t2.b and t1.a = t.id)(+)
order by 1,2;
was transformed to
select
t.id, dcl.rn
from t,
(select rownum rn
from t1,t2
where t1.a=t2.b) dcl
where dcl.a(+) = t.id
order by 1,2;
And it confirms that rownum was calculated on the different dataset (t1-t2 join) without join filter by table t.
I created SR with Severity 1 (SR #3-19117219271) more than a month ago, but unfortunately Oracle development do not want to fix this bug and moreover they say that is not a bug. So I think this is a dangerous precedent and probably soon we will not be able to be sure in the calculation of rownum and old fixes…
Oracle ADF or Oracle JET - An Update
Back in 2015 I wrote a blog entry to help our customers get a clear picture about the Oracle ADF vs Oracle JET "debate", over the years I kept pointing people to this writeup as a simple summary of what we are aiming to do and as a guideline for picking the technology you should use. However, the technology world is an ever changing place, and I believe that it is time to do a bit of an update of that entry to reflect the current status.
While the basic distinctions between the two technologies have not changed, specifically:
- ADF for Java developer, JET for JavaScript developer
- ADF is server side framework, JET is client side focused
- ADF is both front and back-end framework, JET is UI layer only
Some of the surrounding eco-system has changed and evolved.
Client-Centric Architecture MomentumOver the past several years we saw an increase in the adoption of UI development that is focused around JavaScript-based client layer. This is something you can see for example in the results from the Stackoverflow yearly survey - back in 2016 JavaScript was used by 54% of developers and in 2018 the number stood at ~70%.
Today, most of the new projects that we see being developed will pick up a JavaScript based UI layer as their solution for front end development. The adoption of this JS/HTML UI is not limited to web apps, many mobile apps also leverage the architecture to create mobile optimized UIs deployed on devices using either a hybrid or a Progressive Web Application (PWA) architecture. Development organizations like the fact that a client-centric JavaScript/HTML architecture allows them to use a single set of skills to support multi-channel apps.
Back in 2015 many of the Oracle PaaS services were already leveraging this architecture along with Oracle JET - Oracle's Toolkit for building JavaScript based UIs. Today this trend started to extend to Oracle SaaS - Oracle's cloud apps offering. Several of the modules in Oracle SaaS have introduced UIs built with Oracle JET - leveraging its capabilities to create engaging and dynamic user interfaces. In the coming years you are going to see more and more modules offering new UIs based on Orale JET. This is a transition for Oracle's SaaS org from server-side generation UI to a client centric architecture.
In parallel Oracle JET also added a bunch of capabilities targeting mobile specific scenarios including the ability to render the components to look like native controls on both iOS and Android, the ability to package apps as hybrid mobile apps, and support for offline usage patterns. All those capabilities are further pushing the usage of Oracle JET at Oracle as the UI layer for the various mobile apps we deliver.
One thing to keep in mind, since JET only provides the UI layer, there is still a need to build the backend of the application. This backend will handle data access and business logic - and it will then expose REST services that will be consumed by the JET UI. The existing backend layer for the Oracle SaaS apps was developed with Oracle ADF Business Components, and now they are able to leverage the investment they already did in that layer and expose these components through REST services. This way they get to keep the complex logic already implemented in that layer and just create a new modernized UI layer.
If you track down the "what's new" document for the versions of ADF that we released since 2015 you'll see that enhancing the out-of-the-box functionality we provide around REST enablement has been a focus area for us. As a result of this effort, when you are exposing ADF BC as REST you get capabilities such as pagination, query by example, dynamic sorting, relationship management, and more built-into the services you expose.
This approach is something that ADF customers should consider if they decide to adopt the trend of new UIs being built with JavaScript based toolkit. Regardless of the UI framework that you'll choose, remember that the investment you made in the ADF backend can still be leveraged easily.
Better ToolingOne of the other difference I quoted between ADF and JET was the development experience. ADF was focused on providing a more visual and declarative approach to Java developers, while JET had you use code editors to manually write your code.
This is another thing that changed since 2015. Oracle now offers a development platform for JET applications that is focused on a more visual way of building the app - this is Oracle Visual Builder Cloud Service. If you haven't seen a demo before - take 10 minutes and watch this video as an example. As you can see, Visual Builder provides a WYSIWYG UI design, action flow diagrams for business logic, page flow diagrams, property inspectors and more. All this while still allowing you to directly access the code when needed for direct manipulation.
Image 1 - Visual Oracle JET Development in Oracle Visual Builder (Components, Structure Pane, Visual Layout Editor, Property Inspector)
With this environment you can become much more productive building UIs that leverage the power of Oracle JET. Note that Visual Builder is not just about simplifying the layout of your pages, it is also simplifying how you work with variables, binding and business logic - reducing the amount of coding you need to do.
Visual Builder also simplifies the learning curve for people who are new to JavaScript development - making the transition to this new technology smoother. Our SaaS teams have also decided to leverage VB to help them deliver new generations of UI faster.
For customers who already have ADF based applications, another appealing aspect of VB is its ability to understand the structure of ADF BC based REST services and the functionality they provide. Check out this demo to see how you can quickly use Visual Builder to build a JET based mobile app on top of ADF BC REST backend in a declarative way.
A little side note - beyond the ability of VB to create UIs, you can also use it to quickly create tables in a database and business objects (including business logic) on top of them that are exposed as REST services. The technology that drives that layer of VB is based on the same ADF Business Components - another thing that will be of course familiar to ADF developers if they decide to pick up Visual Builder.
SummarySo what should you do if you are an Oracle ADF customer today? As always the answer is "it depends" :-)
The first thing to think about is - "Does Oracle ADF answers my needs and does the application I built with it work as expected?" - assuming that the answer to both is positive, then you can continue as you were.
If, however, there are technical or architectural needs where your existing ADF app doesn't provide the answers you need (for example on the UI side or the mobile device support needs), looking at Oracle JET and Visual Builder is definitely something you should do. Also if you are about to embark on a new project, you might want to consider if a new client-centric architecture for the UI layer is something you can benefit from, and if you think it is - again try out JET and VB and see how you like them. In both cases remember that you can leverage your existing investments in the business objects layer and expose it as REST services to be consumed by new UIs. In fact you can have the two apps running in parallel.
Image 2- JET based architecture (MVVM) compared to ADF Architecture (MVC) - sharing business services
One more note, many developers like to keep their skills fresh and learn new things, if you are an Oracle ADF developer, I think that picking up Visual Builder and playing with it can get you on the road to learning new JavaScript skills quite quickly while still keeping you in a fun friendly visual cloud IDE - give it a try.
Region only shown in development mode in Oracle APEX
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
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 Linux hangs after “probing EDD” in Oracle Cloud
Just short note: If your imported Oracle Linux image hangs on boot in the Oracle cloud, just set GRUB_DISABLE_UUID=”true” in /etc/default/grub
The best free database... Google is wrong!
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
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.
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
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).
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.
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.
Upgrading of Oracle APEX 18.x and future
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.
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 :)
Mind Over Matter
DB Hart commenting on the new translation of Plotinus's Enneads.
Displaying an "Unsaved Changes" Warning in Visual Builder
End-users are strange. Sometime they need the system we are developing to remind them when they do silly things. For example some users want us to remind them if they are trying to navigate away from a page where they did changes to data, but didn't click "save". Below I'll show you an approach for implementing such a behavior in Oracle Visual Builder Cloud Service.
What we are going to do is cancel navigation actions until users acknowledge that they are ok to leave the page.
I will leave it up to you to decide when this should go into effect. While some people might claim that this should be the default behavior across the app, this is debatable. For example, if I go into a "create record" type of page, and then without doing any changes decide to leave that page - should I be prompted for a "you have unsaved changes" message? Isn't me leaving the page the equivalent of saying - I don't need to do changes? As you can see the decision is not clear cut - so in the demo below I let you control when we start this "changes were made" status simply by pressing a button. In real applications these can be done for example when a value in a field changes. At the end of the day, all you need to do is set a boolean variable that tracks whether we are now in "changes were made" status.
In the demo I added a simple dialog to the shell page (the page that acts as the containing template to the rest of the pages) - this dialog has a "you have unsaved changes, are you sure you want to leave?" type of warning and two buttons "Yes" and "No". (Quick tip/reminder - you can see how to add a dialog to a page here, and don't forget to include the popup import in the page's json file).
I add an action chain to the shell page that will be invoked on the vbBeforeExit event - in there I check the value of the "changes made" variable and if changes were made - I show the dialog. Then I use a return action to return an object type variable that has a boolean variable called "cancelled" set to true. Returning such an object tells the flow to stop the navigation.
Now all I needed to add were action chains to the buttons for "yes" and "no" to close the dialog, and for the "yes" scenario to also set the changes made boolean variable to no - so the next time we click to navigate away we don't show the dialog.
Check out the video to see the runtime behavior and the various parts that make up the solution.
Robinson Jeffers
The website for Tor House visits, a fascinating experience:
http://www.torhouse.org/
Pages
