Embedding Files within APEX: Supporting Objects

This is part two of a three part series on embedding files in your APEX application. You should read all the articles (Part 1) before deciding whether or not to leverage this feature in APEX as it has its pros and cons.

A major misconception when uploading a file into APEX is that it will be included when you export the application. Though I agree that makes the most sense, unfortunately it doesn't work this way. You need to explicitly include an installation script that will copy the file into the workspace where the application is being imported to. Sound confusing? Unfortunately it is.

In the previous post I uploaded a file into an application. If I were to export then import this application into another workspace that file wouldn't be included. To explicitly include the file you need to create a Supporting Object installation script:

- Go to Application > Supporting Objects > Installation Scripts and click the Create button.
- At the bottom, under the Tasks heading select "Create Scripts to Install Files".

 - Select the file that is required for the application and click the Create Script button.

At this point in time, APEX will encode (base64) the file (in it's current state) and create a PL/SQL installation script that can be run when you import the application. If you change the file (in Shared Components > Static Files) it has no impact on the installation script (i.e. it's a snapshot of the file at this point in time).

Not updating the installation script with the latest version of the file is where a lot of issues occur when migrating the application from different environments. After someone initially creates the installation script, there's a high probability that you may change the base file, forgetting to update the installation script. This is one of the main reasons why I don't usually recommend embedding files into my application.

Part 3 of the series will cover some issues with embedding files in an application.

Embedding Files within APEX: WORKSPACE_IMAGES vs APP_IMAGES

This is first of a 3 part series on embedding files in your APEX application. You should read all the articles before deciding whether or not to leverage this feature in APEX as it has its pros and cons.

APEX allows you to embed static web files (CSS, JS, images, etc) into your application. This functionality removes the need to store web files on a web server which is required for some applications.

To upload your file into the application go to Shared Components > Static Files. Click the Create button. On the Create page you can upload a file and either associate the file to a specific application or no application.

Files associated to a specific application must have a unique filename within its parent application. It can then be referenced (most likely in a page template) using the #APP_IMAGES# substitution string. Ex: #APP_IMAGES#test.js

Files that are not associated with a specific application are available to all the applications within the workspace and can be referenced (most likely in a page template) using the #WORKSPACE_IMAGES# substitution string. Ex: #WORKSPACE_IMAGES#test.js

Files that are added to the application aren't stored on the web server. They are stored in the database. For high traffic applications this may not be a great idea and you may want to look at storing them on a web server.

Part 2 of the series will cover how to include the files in a installation script.

Oracle Magazine - On Code Reviews and Standards

Last year at Kscope 12 in San Antonio I gave a different kind of talk than I normally do. It was called "Building a Better Team" and focused on the importance and value of doing peer reviews and having coding standards.

It just so happened that Jeff Erickson, from Oracle Magazine, was in attendance. Following my talk Jeff asked me to do an interview for the Community: Up Close section of the magazine. The article, On Code Reviews and Standards was published this month! I've also included the video interview below.



If you don't already have a peer review process implemented in your organization I hope this article will help motivate you to start one.

ODTUG Marketing Committee

In case you missed it on the ODTUG blog, I along with Bambi Price are starting a new Marketing Committee and are looking for some volunteers to join the committee. All the information can be found in this article.

If you're looking to get involved with the ODTUG community and/or passionate about marketing this is an excellent opportunity for you. I look forward to working on this committee and help with ODTUG's marketing efforts.

Decoding Decode Data Types

I ran into a strange issue in APEX a while ago which was due to a decode statement not returning the data type that I expected. Here’s a sample of what the query looked like:

create or replace view mdsouza_temp as
select
  decode(job, 'PRESIDENT', null, 0) col_decode,
  case when job = 'PRESIDENT' then null else 0 end col_case
from emp;

At first glance you would expect both columns to be the same. If you query the view they look like they return the same values but they don’t (this is what caught me). Upon further investigation the COL_DECODE column actually returns a VARCHAR2 and not a number:

desc mdsouza_temp;

Name       Null Type        
---------- ---- -----------
COL_DECODE      VARCHAR2(1)

COL_CASE        NUMBER     

It turns out that if you have a NULL as the first result (not the last, default, value) the returned value/data type will be a VARCHAR2 and not the data type of the other values as shown in the following example:

create or replace view mdsouza_temp as

select
  decode(job, 'MANAGER', 1, 'PRESIDENT', null, 0) col_decode,
from emp;

desc mdsouza_temp;

Name       Null Type  

---------- ---- ------
COL_DECODE      NUMBER

If you do have to have NULL as the first result in the set you need to explicitly convert the result to the appropriate data type:

create or replace view mdsouza_temp as

select
  to_number(decode(job, 'PRESIDENT', null, 0)) col_decode
from emp;

desc mdsouza_temp;

Name       Null Type  

---------- ---- ------
COL_DECODE      NUMBER   

This is not a bug, and is covered in the Oracle documentation on DECODE "Oracle automatically converts the return value to the same data type as the first result."