Monday, April 04, 2022

Export multiple tables to one Excel with different sheets in Oracle APEX

Today there was a question on how to export data from the Oracle Database and Oracle APEX into one Excel file with multiple sheets. Each sheet contains data from a different table.

With APEX Office Print it's a matter of a few minutes to fulfill this request 😀

If you have never heard of APEX Office Print (AOP), here's one paragraph on it.  AOP is the easiest to use product/plug-in that fully integrates with Oracle APEX to give you all the reporting and export functionality you've dreamed of. You create templates in Word, Excel, PowerPoint, HTML, or Text and AOP will merge it with the data coming from your database, APEX components, or REST Web Service and return a document to you in the output format of your choice (PDF, Word, Excel, PowerPoint, HTML, Text). If you want to get started with AOP, here's a quick Getting Started with AOP video.

Let's get back to this example where we want to generate a single Excel file, with multiple sheets and each sheet contains data from a different table.


Step 1: Prepare your SQL statement.

In this example, we want to export the data coming from our customers, products, and orders table.
AOP understands the CURSOR Expressions of SQL. Each cursor will contain the columns of the different tables. You can have as many cursors as you want, look at them as blocks of data.

select
  'file1' as "filename", 
  cursor(
    select  
      cursor(
        select
          cust_first_name,
          cust_last_name,
          cust_city
        from aop_sample_customers) as "customers"
    , cursor(
        select 
          order_id, 
          order_total          
        from aop_sample_orders) as "orders"
    , cursor(
        select 
          product_name, 
          product_description,
          category,
          product_avail,
          list_price
        from aop_sample_product_info) "product"
    from dual) as "data"
from dual

Note that with the cursor expression you can also create more nested data (as seen in the AOP Sample App on page 30). For example, if you want to see all orders and order lines of a specific customer, your SQL statement would look like this:

select
  'file1' as "filename", 
  cursor(
    select
      c.cust_first_name as "cust_first_name",
      c.cust_last_name as "cust_last_name",
      c.cust_city as "cust_city",
      cursor(select o.order_total as "order_total", 
                    'Order ' || rownum as "order_name",
                cursor(select p.product_name as "product_name", 
                              i.quantity as "quantity",
                              i.unit_price as "unit_price" 
                         from aop_sample_order_items i,
                              aop_sample_product_info p
                        where o.order_id = i.order_id
                          and i.product_id = p.product_id
                      ) "product"
               from aop_sample_orders o
              where c.customer_id = o.customer_id
            ) "orders"
    from aop_sample_customers c
    where customer_id = :CUSTOMER_ID
  ) as "data"
from dual

So you can add the CURSOR() expressions on the same level as well as in a hierarchy.

Now that we understand this, let's get back to our example, where we wanted to export the data from the SQL statement to different sheets in Excel.


Step 2: Create your Excel template

There are a few ways to create your own template:

  • You can start from a new empty Excel document,
  • or you can start from a template of your company (e.g. with the logo and some house branding),
  • or you can download a template you like from Microsoft's Excel template repository,
  • or you can let AOP generate a starter template for you.
We will use the last option. Nothing easier than letting AOP create a template for us!

AOP has a PL/SQL API or an Oracle APEX plug-in you can use. In this example, we will use the plug-in. I won't go over how to install the Oracle APEX plug-in, you can find that in the getting started video.

So, on our Oracle APEX page, we create a button and a dynamic action calling the APEX Office Print plug-in. We specify that we want an AOP Template, we add the SQL statement as the data source, and select an Excel file as the output type. Here's what it looks like in Oracle APEX:


When clicking the button, this is the Excel file AOP generated:


The AOP template will add the tags (see lines 4, 7, and 10 between {}) for the data you specified in the SQL file, and give some more explanations below to explain what is possible with AOP. For example, AOP can also generate sheets or use formulas dynamically.

To finish our template, the only thing we need to do is to copy lines 7 and 10 and put them on their own sheets. Finally, we customize the template exactly as we want. For example, we can give it a header, make some tags bold and make it really pretty with our company logo.


Once we are happy with our template, let's upload it to Static Application Files in our app. Note: you can store the template in your own table, the filesystem, online, anywhere that AOP can access.


Step 3: Specify the template and Done!

Finally, specify the new template in the AOP Dynamic Action. The Template Type is Static Application Files and the Template Source is the filename.

And presto, you are done! Click your Excel button again and here's the output:


So any time you need to create a report in Excel with data coming from Oracle APEX or the database, you know you can do it with APEX Office Print (AOP)! 😁

Monday, November 08, 2021

Oracle APEX 21.2: some things to unlearn

A few days ago Oracle Application Express (APEX) 21.2 was released. You can now upgrade your own environment with this new release. 

With every new release, it's important to look at the Release Notes. One section I'm particularly interested in is the Deprecated Features. Features listed in this section will still work, but sometimes in the future it might not, so it's a good time to start unlearning to use those features. Typically these features are not really necessary anymore or got replaced by something else, but it also occurs that some things get changed over time to make things more consistent or logical for new people.

One deprecated feature in APEX 21.2 got my intention... The use of the following substitution strings #APP_IMAGES# and #IMAGE_PREFIX# should from now on be avoided. Instead, make use of #APP_FILES# and #APEX_FILES#. Here's a complete list of substitution strings that should not be used anymore:

I will need to get used to not using #APP_IMAGES# anymore, as I used that from time to time when constructing a URL for a file in Static Application Files. 

Another deprecated feature is some region positions like After Header and Before Footer, next to Body 1, Body 2, and 3.

Monday, October 11, 2021

Oracle APEX Support from 5 years to 3 years #JoelKallmanDay

If you are still running Oracle APEX 5.1, it might be time to schedule an upgrade. Support is running out at the end of the year. 

Does it mean you can't use Oracle APEX 5.1 after December 2011? No, not at all, if you have no need to upgrade, and all is running smoothly, it's fine... you just can't request a patch anymore.


Till the 20.2 release you could stay on an APEX release for 5 years, but that has changed since the 21.1 release. Oracle announced it will reduce the support for an APEX version from 5 years to 3 years.  

As Oracle APEX is a web development tool and things change so fast on the internet, I would recommend upgrading faster than every 5 years anyway. From a security and browser/mobile compatibility point of view, upgrading makes all the sense. Having said that, if you run an app internally and don't upgrade browsers within your company, I can understand if you just keep what you have and don't want the hassle of upgrading. Upgrading Oracle APEX is typically a breeze, but still testing is required and some things might behave slightly different or became deprecated.

At United Codes we keep 3 instances on our main server: Oracle APEX 18.1, 19.2, and the latest version of APEX, to which we upgrade very fast. The 18.1 release we use as a base for most APEX plug-in development, unless we need some specific plug-in features for which we need to use 19.2 (e.g. for IG support). 

With APEX Office Print we try to support all features of APEX, e.g. when the new maps component came out, we made it so you can include it in your documents. So for that development, it's crucial to stay on top of the game. To most of our customers, I give the advice to wait for the first or second patchset, depending on our own findings.

I wrote this post for Joel Kallman Day, a day we push out content as a community and try to generate a bit of buzz.

Saturday, July 24, 2021

Hiding spinning icon in APEX 21.1 Modal Dialog

An AOP customer wrote us that when opening a PDF in a Modal Dialog the spinning icon didn't disappear automatically anymore in Oracle APEX 21.1, whereas it did before.  


I created a use case but saw it not only affects the AOP process but any file you want to open in the Modal Dialog. With previous versions of APEX, this didn't happen, but with APEX 21.1 the Modal Dialog keeps showing the spinning icon.

Here's an animated gif that shows the use case:
I created two use cases; both buttons redirect to a modal page in the app.

On the modal page behind the "View File" button, there's a before header process which gets a BLOB (PDF) from the database and displays it inline.


On the modal page behind the "AOP Modal" button, there's a before header AOP process which generates a PDF of a classic report on the fly.


To work around the issue, I added a dynamic action on the page where the buttons are defined; on click of the button, it will execute the following JavaScript code:

setTimeout(function(){ 
  $('.ui-dialog--apex.t-Dialog-page--standard .ui-dialog-content').addClass('js-dialogReady');    
}, 2000);

After waiting 2000 milliseconds (2 seconds) it will add the js-dialogReady CSS class, which tells the icon to hide.


Hope it will help you in case you need to remove the spinning icon from your modal dialog.

Tuesday, June 08, 2021

Have extra EURO 2020 fun with this Oracle APEX app

For the last 15 years, with every big football tournament (World Cup and Euro Cup), we've launched an Oracle APEX app where you can predict the scores of the games. You can find this year's app at https://euro2020.unitedcodes.com

Features

Many people do a challenge with their friends or company, for fun, for money... Instead of using a custom-made Excel, use of email, or use of paper, you can use the Euro 2020 Challenge site to track who's in the lead.

  • You can enter the scores of every game and see how well your predictions compare to others (overall).
  • You can create a group e.g. your company, or a team, etc and see how well you play within your group.
  • You can see statistics of predictions

History

15 years ago, I built a site to promote the use of Oracle APEX. People loved it, so I decided to update the site with every new release of APEX. You can find some more history and screenshots of how the app looked here. Not that many changes have been done compared to the app from 2018, but this year it runs on Oracle APEX 21.1, so it shows that upgrading APEX apps is smooth.

Oracle APEX features

When upgrading to the new Oracle APEX version, the Advisor and Upgrade assistant were run. Some plug-ins (e.g. select2) got replaced by built-in features (e.g. Inline Pop-up). Most changes were done in the Admin section, where tabular forms were replaced by Interactive Grids, native Forms regions are now being used, and other parts that showed up in the upgrade assistant were applied.

Social authentication was updated, so you can sign-up and log in with Email, Facebook or Google.

Normally with an upgrade, we also update the Universal Theme version, but as it's using a custom theme we didn't do it for this app.

Go and play!

So, now head  to the site/app and start challenging your friends!


Good luck!

Saturday, June 05, 2021

Upgrading Oracle APEX has never been easier

On May 12th Oracle APEX 21.1 was released. 

This post is not about why Oracle APEX 21.1 is worth an upgrade. You can see for yourself if you like the improvements in the Release Notes. Some companies upgrade once a year, some wait for the patch set bundle, and some upgrade very fast.

We upgraded our production instance this weekend. I blogged about how we do the upgrades and try to minimize downtime in the past. But upgrades have become even easier!

While the Oracle APEX kept running; 15 minutes install which of 5 minutes downtime, and we were done!

Note: it's always good to read the installation guide

Below what we did: 

  1. Download Oracle APEX
  2. Unzip
  3. Go into the directory 
  4. Connect to the Oracle Database with SQLcl or SQLPlus 
  5. Run 1 command:
@apexins.sql SYSAUX SYSAUX TEMP /i/

That was it! ... the installation did everything for us... and again this was in a live environment!
ORDS knows now that an APEX install is busy and suspends traffic for a certain time and next it comes up again:

Phase 1: no downtime


Phase 2 and 3: small downtime during phase 3 switch:


And done:


During Phase 3 downtime, I copied the new images folder.

I believe it wasn't really necessary as everything was working, but I did run the ORDS validate after the APEX install just to check it (while ORDS was still running):


And the proof of the max 5 minutes downtime:


I'm really impressed by the install and upgrade, it's basically an autonomous process these days 😀 

Monday, May 31, 2021

In memory of Joel Kallman

On May 26th, now a few days ago, the world stood still for a moment for me. When I read that Joel passed away, I didn't know how to deal with it. If it's already hard for me, what about his family and the APEX team... it was a storm in the whole Oracle APEX community, and all of us were shocked and wish his close family and friends the most support.

It just hurts as Joel was such an amazing person, both on a personal level and professionally. I don't know anybody else who's so unselfish, understanding, and approachable as Joel. He was a great person all-around. 

After Carl and Scott, it's the 3rd person who leaves my Oracle APEX family too soon.

Joel was employee number 1 of Mike Hichwa, and they both created and supported APEX from day 1. 

The moment I saw APEX (HTMLDB) it was love at first sight. I became passionate about this great Low Code Development Tool! When I wrote the World Cup Challenge in 2006, to promote what you could do with Oracle APEX, Joel was one of the first to reply to my blog post.


Later on that year I followed his session at Oracle Open World "Building Real-World Solutions with Oracle Application Express" 

That Oracle Open World was legendary, as we held the first-ever Oracle APEX Meetup 

I reread that post, and already back then I wrote: "One thing is sure: Michael Hichwa and Joel Kallman really want to support us!" 

And boy they did... Joel became the face of our community, a true leader.

Many more conferences followed, where APEX presentations were always successful. Kscope became a special place where not only the APEX content was at an exceptional level, it was also the place where many people from the APEX Dev team came together and mingled with the community. Not only was everybody very accessible during the conferences, even during the evening activities and parties we had so much fun, and Joel went always "all-in"! From serious conversations to just laughs and playing poker.

In 2014, after a difficult time for me, I had a really nice chat with Joel. He told me he would pray for me and I will never forget his support.

I appreciate Joel tremendously both on a personal as professional level. He helped so many people, including me. Did you know that Joel, single handedly installed APEX Office Print at Oracle in 2016? He supported us in many ways, I can not thank him enough.


Joel was not only a great coder and manager, but he was also a genius in marketing and community building. In 2015 he launched "LetsWreckThisTogether", together with the community we would make a change in the adoption of Oracle APEX. Joel frequently gave the keynote speech at APEX World. Here Joel went with attendees that won a prize at APEX World on a boat trip.


In 2018 there was Joel's legendary keynote  to "Make Oracle Cool Again"



In 2020, when COVID hit, he sent us an email that the day had come :


We are now in 2021, and we made it... Oracle APEX has never had more attention from inside and outside of Oracle. There have never been more people using Oracle APEX, almost all companies using Oracle are now investing in Oracle APEX and the APEX community has never been so big.

I love Mike's words: "Joel set the tone for how we could all work together. Such a great legacy.  Such a great community.  So international, no BS, just the way things should be.  I think everyone knew that Joel was about community success and he had your back."

Let us continue with our Oracle APEX community and share our knowledge and help others.

Thank you, Joel, you were not only a great community member, but you were also a great friend. 

The link to the official page of Remembering Joel.