Tuesday, October 18, 2022

Oracle APEX Form: Save before Print

In almost every Oracle APEX application I create a Report with Form to edit the data.

On this Form many people add a Print button to generate a document for this specific record.

From time to time I get the question how to first save the record and next print or download the document.

Here are the 5 steps to do this:

1. Duplicate the Save button and name the button Print. Or alternatively create the Print button from scratch and make sure to set the action to Submit, the Database Action to SQL UPDATE action, and make it conditional to only show when the PK is not null. 

This will make sure when the automatic row processing is performed the update is happening.

2. In the processing section, create a Branch with a Server-side Condition when button pressed PRINT. 

When clicking the Target button in the Branch, link to the same page, and pass the item with the primary key and most importantly set the Request to PRINT.

3. Create a new Dynamic Action called AOP Print which runs on Page Load with a Server-side Condition that the Request value = PRINT (as we defined in the Branch).

4. Add as True Action the APEX Office Print (AOP) dynamic action (DA) plug-in. 
Specify the template and data (SQL) and the output you want (PDF). We want the document to download into the browser.

Optionally, add another True action to Close the Dialog in case your Form is a dialog and you want it to automatically close.

5. When you download the document, it might be empty. This is because our PK isn't known to the AOP process. See my previous blog post which goes in more details. So the final step is to set Maintain Session State: Per Session (Disk) for the primary key item.

And that's it... now when you click the Print button, it will save the record first and next download the document to the browser (and optionally close the dialog of the Form).

Tuesday, October 11, 2022

No value of a page item when using a Dynamic Action

Sometimes you expect to see some data in your Oracle APEX application and it's not there... in this post I will describe one use case I see more often and how to solve it.

We have a report which shows all the employees (coming from the EMP table):

Clicking the pencil icon opens up the form of that employee:

We added two extra buttons

  • a print icon which creates a document for the employee
  • a bug icon which adds 10% of the salary in the commission field

Let's first start to see what is behind our Print button. We use the APEX Office Print (AOP) Dynamic Action to generate a document, but when opening the document it's empty. 

Behind our Bug button we have a Dynamic Action that fires a process and sets the value of an item when P2_EMPNO is not empty, but for some reason the commission is not being filled in.

In both cases, we don't get the data that we expect to see...

Whenever you have an issue like this, I turn APEX debug on (see APEX developer toolbar and click Debug > App Trace), click the button again and check my debug output (Debug > View Debug).

This is what I saw in the debug output after clicking the Bug icon:

We can see that the EMPNO item is empty, so our IF statement in our code is returning false, and the commission is not being calculated.

By default, APEX puts the primary key column as a hidden item on the page. The following two settings of the item are important: Maintain Session State is set to "Per Request (Memory Only), and Session State Protection is set to "Checksum Required - Session Level".

The error in the debug output was "Access to undefined Per Request (Memory Only) variable P2_EMPNO", which doesn't allow the value of the item to be known in dynamic actions (AJAX requests). One way to solve this is to change the "Maintain Session State" to "Per Session (Disk)".  

And now, on the debug output, we see the value of P2_EMPNO being filled in, and the commission is being calculated.

Also, when we Print the document, we now see that the employee is being filled in.

Yay! We now see our data! 😀 

You might wonder why did this solve my issue? When session state is maintained "Per Session" the value is stored in the database and available across requests, whereas when "Per Request" is set, the value is only available during page load.

An alternative to the above solution is to define in your dynamic action the item you need the value of in "Items to Submit". But with session state protected items, depending on the use case, this might not always work, and you get a checksum error.