Static files outside APEX Images folder

Recently I attended a lecture about good (and bad) APEX practices, where I heard the one about the place to store your static application files (JS, CSS). The recommendation was that these files should not be kept in the APEX Images folder on your Web-server, but that they should be stored in a separate (dedicated) folder. The reasoning behind this is simple: minimize the risk of overwriting or losing your files, when doing an APEX version upgrade. As this upgrade requires you to copy a new APEX Images folder to your Web-server, you can see how it could be beneficial to keep your own custom files separate.

As I haven’t done it this way before, I tried to make a setup to support it. In this blog post, I will describe how I separated my files on a local installation of Oracle XE 18c. My local instance is set up like this:

• Windows 10
• Oracle XE 18c (18.4)
• Apache Tomcat 8.5.4
• ORDS 19.1
• APEX 19.1

The installation guide for basic Tomcat+ORDS setup (Link) tells us to copy the APEX Images folder from the installation archive to the webapps folder of the Tomcat installation directory, and then to rename it to “i”. If we put some of our own files into this directory, they can immediately be referenced in APEX by using the #IMAGE_PREFIX# substitution string, e.g. #IMAGE_PREFIX#apex_custom/css/my_styles.css
But instead of this, you can use a path anywhere on your computer and leave the APEX Images folder untouched. I did this by creating a folder and then defining a PostResource pointing to this folder, in the Tomcat configuration.
To do this, make the following steps:

1) Create a new folder for your static files, e.g. apex_custom. For simplicity, I created it in the webapps folder, but outside the "i" folder. I Also created separate subfolders for CSS and JS:

2) Put your static files inside the apex_custom folder, e.g.:

3) Open the server.xml file from the Tomcat installation directory:

..\Tomcat 8.5\conf\

4) In the <host> tag, add a reference to your folder, changing the path according to your own installation setup:

<Context docBase="C:\Program Files\Apache Software Foundation\Tomcat 8.5\webapps\apex_custom" path="/apex_custom" />

Here is a screenshot from the file itself:

The Context docBase "path" property is a symbolic name by which your folder will be accessed, but to keep things simple, here it is the same as the folder name. You can change this to your needs and you can add any folder on your computer this way.

5) Save server.xml file changes and restart your Tomcat server, e.g. in the Command Prompt:

net stop Tomcat8, and afterwards

net start Tomcat8

6) To test if your files can be accessed by Tomcat, try to reference them in the URL of your browser:

You should see the file contents. If you get the 404 error, review your previous configuration steps.
The same files in N++:

7) Let’s move on to APEX. To reference this new folder, go to Application Properties of your App, and in the bottom of the Definition tab you will find Substitution Strings.

Define a new string APEX_CUSTOM_JS, with the value of /apex_custom/js/

Also, define a new string APEX_CUSTOM_CSS, with the value of /apex_custom/css/

Apply Changes.

8) You should now be able to reference your static files:
a. On Page Template level,

b. Or on Page level

* Tip: If you need to edit Templates, you should create a copy of an existing template and then make changes to the copy. Never unsubscribe default templates from the Universal Theme.

9) Finally, test it in your App:

Cheers!
Daniel

APEX 5 IR Conditional Columns in Export

This is a post about exporting Interactive Report data to CSV, where we want to preserve leading zeros in columns which contain data in such format. In a simple example, if we try to do a straightforward export, we get this result:

If we want to preserve leading zeros, we need to use an old trick which was described in a couple of posts in the past, like this one:
http://www.talkapex.com/2010/06/how-to-only-display-column-when/

As this still is a viable solution with one Interactive Report per page, there is a twist in Apex 5: multiple Interactive Reports on the same page. To achieve the same result on a page with more than one Interactive Reports, we have to dig a little deeper.
 
In the example from the begginning of this text, I use EMP and DEPT tables joined, where EMPNO and DEPTNO columns are formatted to display leading zeros.

These are the steps to achieve our goal:

1) To preserve leading zeros, we need to concatenate =" to the start of our columns EMPNO and DEPNO and " to the end of our columns, so we have one display column and one export column (ending with "_ex"):

2) Next step is to arrange our columns in a desired order and then save this view as a default report:

 

Here you can see that the Column Heading for export columns is the same as for display columns.

3) Here comes the tricky part. There are two IRs on my page, so in the Region Properties for my IR from which I am exporting, I set the Static ID attribute to rgnEmpIR2 (of course, this can be anything, according to your developer needs):

4) After setting the Region Static ID, we can conclude our effort with setting Server-side Conditions (only "Conditions" on Apex versions prior to Apex 5.1) on our columns of interest. Display columns should be visible only in runtime of the Apex Page, while Export columns should be visible only while exporting.

For display columns (EMPNO and DEPTNO), we set Server-side Condition to Request != Value, and the Value to IR[rgnEmpIR2]_CSV:

For export columns (EMPNO_EX and DEPTNO_EX), we set the opposite condition:

As you probably already noticed, the Request has a structure of 

IR[ + Region Static ID + ]_DOWNLOAD_FORMAT

So you can use this technique for RTF, HTML and other IR Export formats.
 
And that's it, try the Live demo here:
https://apex.oracle.com/pls/apex/f?p=DONT_PANIC:IR_COND_COLS


Cheers,
Daniel