How to Find Current APEX Version

Last week my DBA asked me what version of APEX we were running. Instinctively I loaded up the APEX development page and looked in the bottom right hand corner:



This worked, however he needed to dynamically obtain the current version for a script that he was writing. After some digging around he sent me the following query:

SELECT * 
FROM apex_release

Note: this isn't earth shattering but more for my reference

Conditional Validations in APEX

There may be some instances when you have validations on your APEX page that you only want to run if all the other validations have passed. A good example of this is if you have 2 fields, FROM_DATE and TO_DATE and have the following validations:
- V1: Ensure FROM_DATE is not null and is a valid date
- V2: Ensure TO_DATE is not null and is a valid date
- V3: Ensure FROM_DATE < TO_DATE

In this example you only want the last validation to run if the first 2 pass (i.e. both FROM_DATE and TO_DATE are valid dates). Currently there's no declarative way of doing this. i.e. validations don't support declarative dependencies.

A simple trick to get around this issue is to put a condition in some validations to only run if all the other validations pass. This may not work in all situations, however it should help you in some.

In the example about, modify the 3rd validation (V3) and set the condition to the following:
Condition Type: PL/SQL Expression
Expression 1: apex_application.g_inline_validation_error_cnt = 0

Now V3 will only run if all previous validations pass.

Special columns for APEX Standard Reports

I was testing APEX 4.0 on http://apex.oracle.com and I noticed that on Standard Reports you can add 2 types of "special" columns: Derived Column and Column Link. At first I thought this was a new APEX 4.0 feature, but then tested on a copy of 3.x noticed it was there as well! I apologize if this is old news to people but I found it to be a pretty neat feature.

The interesting thing about these columns is that you can add Derived Column and Column Link columns and it does not modify your existing SQL.

The only difference I can tell about these 2 column types is that Derived Columns don't contain the following attributes: List of Values, Tabular Form Attributes, and Column Link

How to only Display Column when Downloading

Sometimes you may have a column that contains inline HTML. A simple example of this is if you have a column called "color" and you want to display the color in the report. Your query would look like this:

SELECT '"' || color || '' color
FROM my_colors

Note: That they're ways to get around this simple example using Report Templates and Column Formatting. I'm just using it for demo purposes

If you were to download this report the "color" column would contain all the html (i.e. span tags etc). This may confuse users since they expected to see "red, green, blue, etc..." in their download file, but instead see the colors wrapped in a lot of html.

A workaround that I've used is to create 2 columns: color_html and color

SELECT '"' || color || '' color_html, 
            color color
  FROM my_colors

And modify each report column's attributes:

Standard Reports

color_html
Column Definition:
Include In Export: No

color
Conditional Display:
PL/SQL Function Body Returning a Boolean: return apex_application.g_excel_format

Interactive Reports

color_html
Conditional Display:
Request is NOT Contained within Expression 1: CSV,HTMLD

color
Conditional Display:
Request is Contained within Expression 1: CSV,HTMLD

Now when a user downloads the report they'll get the non-html version of the column. This solution works in APEX 4.0 and APEX 3.x

Interactive Reports with over 100 Columns

I ran into an issue last week where I had an Interactive Report (IR) with over 100 columns. All 100 plus columns displayed for end users, however I was only able to modify the first 100 columns. For example, the 101st column did not display in the report attributes screen (see screen shot below)



After some digging around I figured out how to modify columns that weren't displayed on the Report Attributes screen. This demo requires Firefox and Firebug. You should also be able to do this in Google Chrome. This demo works for APEX 3.x and APEX 4.0.

Build Large IR:

SELECT LEVEL c001, LEVEL c002, LEVEL c003, LEVEL c004, LEVEL c005, LEVEL c006, LEVEL c007,
       LEVEL c008, LEVEL c009, LEVEL c010, LEVEL c011, LEVEL c012, LEVEL c013, LEVEL c014, 
       LEVEL c015, LEVEL c016, LEVEL c017, LEVEL c018, LEVEL c019, LEVEL c020, LEVEL c021, 
       LEVEL c022, LEVEL c023, LEVEL c024, LEVEL c025, LEVEL c026, LEVEL c027, LEVEL c028, 
       LEVEL c029, LEVEL c030, LEVEL c031, LEVEL c032, LEVEL c033, LEVEL c034, LEVEL c035, 
       LEVEL c036, LEVEL c037, LEVEL c038, LEVEL c039, LEVEL c040, LEVEL c041, LEVEL c042, 
       LEVEL c043, LEVEL c044, LEVEL c045, LEVEL c046, LEVEL c047, LEVEL c048, LEVEL c049, 
       LEVEL c050, LEVEL c051, LEVEL c052, LEVEL c053, LEVEL c054, LEVEL c055, LEVEL c056, 
       LEVEL c057, LEVEL c058, LEVEL c059, LEVEL c060, LEVEL c061, LEVEL c062, LEVEL c063,
       LEVEL c064, LEVEL c065, LEVEL c066, LEVEL c067, LEVEL c068, LEVEL c069, LEVEL c070,
       LEVEL c071, LEVEL c072, LEVEL c073, LEVEL c074, LEVEL c075, LEVEL c076, LEVEL c077, 
       LEVEL c078, LEVEL c079, LEVEL c080, LEVEL c081, LEVEL c082, LEVEL c083, LEVEL c084, 
       LEVEL c085, LEVEL c086, LEVEL c087, LEVEL c088, LEVEL c089, LEVEL c090, LEVEL c091, 
       LEVEL c092, LEVEL c093, LEVEL c094, LEVEL c095, LEVEL c096, LEVEL c097, LEVEL c098, 
       LEVEL c099, LEVEL c100, LEVEL c101, LEVEL c102, LEVEL c103, LEVEL c104, LEVEL c105, 
       LEVEL c106, LEVEL c107, LEVEL c108
  FROM DUAL
CONNECT BY LEVEL <= 10

Get the COLUMN_ID

This is the column that you want to modify that isn't currently displayed on the Reports Attributes page

SELECT column_id,
       column_alias,
       display_order,
       report_label
  FROM APEX_APPLICATION_PAGE_IR_COL
 WHERE application_id = :app_id
   AND page_id = :page_id

Run

Go to the Reports Attributes screen (the screen that lists all the IR columns)

Open Firebug and go to the Console Window. Enter the following:

apex_p601_setColumnIDandSubmit('311507131164520006'); // Enter your column ID here

New URL: http://TalkApex.com

Well it's been a while since I've had any new posts on this blog. I've been extremely busy with "after school" activities (soccer, speed skating, biking, etc...)

I don't have an APEX specific post today but I do have a few items to discuss. The first, in case you haven't noticed yet, is that I've changed the URL of this blog from http://apex-smb.blogspot.com to http://TalkApex.com, The old URL, http://apex-smb.blogspot.com will still work so you don't need to update any old links.

Just because I haven't had time to write about APEX, doesn't mean I've forgotten about this blog. I've been compiling a fairly large list of new things to write about over the past few months. I hope to spend a some time soon to write about these items.

Last, but not least, I'll be giving 2 presentations at ODTUG Kaleidoscope. I submitted my presentation for the CD, however I think that they'll change significantly by the time I present. As such I will post the final slides used for my presentation here after the conference. The presentations I'll be giving are

• Enhancing APEX Security: Monday 2:30~3:30


• How to be Creative: Using the APEX Dictionary to Create Solutions: Tuesday 4:45~5:45

Hope to see most of you at ODTUG!

- Martin