Friday, May 6, 2016

Query APEX dictionary views from non-parsing schema

You may have noticed that if you query any of the APEX's dictionary views (APEX_*) outside the parsing schema you won't get any rows. There's quick solution for this. You only have to grant APEX_ADMINISTRATOR_ROLE to this schema.

    grant APEX_ADMINISTRATOR_ROLE to your_nonparsing_schema;

Be careful, user with this role can execute APEX_INSTANCE_ADMIN package procedures and functions.


Tested in APEX

Wednesday, March 16, 2016

Watch out: validating number type items

I've discovered a strange behavior in APEX that I thought it works out of the box. It's about validation of the number type items.

Simple Case

1) I've created two number items on a page, named P16_MIN_VALUE and P16_MAX_VALUE, and a submit button:

2) Next, I've added the validation that checks if P16_MAX_VALUE is greater or equal than P16_MIN_VALUE


If you put 2 in field Min value and 1 in filed Max value, the validation message is displayed on screen (as expected):

If you put 12 in field Min value and 2 in filed Max value, the validation message is NOT displayed on screen. You'll see success message:

The problem (or unexpected behavior) is that APEX doesn't validates number type items as numbers but as strings.

Demo is available here.


I can't say if this is a bug or expected behaviour because there's no documentation about it (or at least I didn't found it). I know there are functions for getting numeric value of an item: apex_util.get_numeric_session_state and NV but I thought if you have a numeric item and you use it as a bind variable that it will be "binded" like a number.

So, the solution is to explicitly convert your items to numbers or to use one of those functions mentioned above. Your validation should look like this:

...or even better, write your logic in packages with number input parameters.

Enjoy! ;)

Tested on APEX

Saturday, February 13, 2016

Tip of the day: APEX mail & max emails per workspace

It was almost perfect winter day. Sunny with fresh snow. Perfect day for running up and down my favorite mountain.

I was enjoying every bit of nature and all of a sudden: Beeep, beeep! The sound of a SMS. Damn! I've ignored it at first. Two seconds later Beeep, beeep! Beeep, beeep! I thought, it must be something important...and yes it was. It was my boss, messaging me that yesterdays PL/SQL job that sends e-mail from our HR APEX app didn't send e-mail to around 500 clients from 1500 total.
Strange thing, but my Mac was miles away and I couldn't help at the moment (and yes, it was urgent) so I've decided to turn around and head home.

First thing that I've did when I came home was checking APEX mail log. No errors there, no mails in a queue - sounds good. I thought it must be something from the processing part that runs in a PL/SQL job. Then I've checked error log and there it was:

You have exceeded the maximum number of email messages per workspace.  Please contact your administrator. 

Omg, what's that?! It's APEX instance parameter that defines the number of email messages that can be sent with the APEX_MAIL API per workspace per 24 hour period. Omg! :)

To fix this you have to log on as APEX instance admin user and go to Manage Instance > Instance Settings > Email:

So, if you don't want to be bothered in those great moments, don't forget to change it. It can save the day.

Also, If you want to query apex_mail_log or apex_mail_queue dictionary views you have to set workspace (security_group_id) before:


Tested on APEX

Wednesday, January 27, 2016

APEX 5: UT outdated browser (IE) message

Here's a quick tip how to put outdated browser message (for older IE browsers) to your APEX application that uses Universal Theme.

If you take a closer look to page templates of Universal Theme in Header property you can see some conditional comments:

They are perfect for this because they have some CSS classes that you can easily use to check IE version (lt-ie*).
I won't go into details about conditional comments. If you need some more info you can find it here.

Next step is to put a region and message that you want to display if user visits your app with outdated browser. In my case I've created it on the login page just above login region, but you can also put it on global page if you want it on every page.

For this region I've defined Static ID property rgnOutdatedBrowser and Custom Attributes property style="display:none" to hide it initially.

Next thing that you have to do is to put inline CSS rule to page header to display this region for specific IE versions (in this example for IE8 and older):

You can use this CSS classes to display region in :
  • IE6 and older
          .lt-ie7 #rgnOutdatedBrowser {display:block !important}
  • IE7 and older
          .lt-ie8 #rgnOutdatedBrowser {display:block !important}
  • IE8 and older
          .lt-ie9 #rgnOutdatedBrowser {display:block !important}
  • IE9 and older
          .lt-ie10 #rgnOutdatedBrowser {display:block !important}

Here is the outcome of example in IE8:

That's all. Enjoy! :)

*Tested on APEX

Tuesday, October 27, 2015

Remove vertical borders from Universal Theme Interactive Reports

In Template Options of Classic Report region you can set property to see only horizontal borders (property Report Border):

What about Interactive Reports? There's simple way to do it. Just open Theme Roller add this two lines of CSS in Custom CSS property:

  .a-IRR-table td{
    border-left:1px solid transparent

*Tested on APEX

Thursday, October 22, 2015

Explore the mysteries of APEX Builder

If you've ever wondered how some things are done in APEX builder there's a simple way to find out. There's only one prerequisite: you need to have access to SYS or APEX_* user.

How to do it? It's pretty simple - open your favorite IDE - SQL Developer and connect as SYS or APEX_* user. If you're connected as SYS in Connections tab go to Other Users > APEX_* > Application Express folder. If you're connected as APEX_* user just go directly to the Application Express folder.

After that you can right-click on application or page that you're interested and explore its components (items, regions, processes, validations...).

Thursday, October 1, 2015

Speed Up Your APEX apps with JSON and Mustache.js

Few days ago I've started to explore solutions to generate (and parse) JSON objects from APEX 5. During this research I came to interesting facts and features.

First of all I found great blog posts from Dimitri Gielis:
and Dan McGhan:
where you can read almost everything that you'll ever got to know about APEX and JSON.

After that I've decided to try it by myself. So I've created simple examples of generating JSON from same source (query from all_objects table) with different methods:
  • Example 1 - using apex_util.json_from_sql (fun fact: not documented)
  • Example 2 - using apex_json package and refcursor
  • Example 3 - using apex_json manual (open_object, wite, close_object)
  • Example 4 - manually with implicit cursor
  • Example 5 - manually with bulk collect
  • Example 6 - using REST service
  • Example 7 - using PL/JSON package

Source in all example was:
SELECT rownum x  
     , owner a  
     , object_name b  
     , object_type c  
  FROM all_objects  
 WHERE rownum <= 500 

Why I was using short aliases in query, like x, a,b,c? Because size of JSON object with short aliases for 500 rows was 29.1 KB and without aliases 43.3 KB (for 7000+ rows with aliases 461.0 KB, without 681.5  KB).
Maybe JSON is not so readable in case of short keys (aliases), but for big JSON object difference in size is significant.

Why only first 500 rows? Because when you generate JSON using REST service there is limit and you can fetch only 500 rows by one request.
Update: you can change this in ORDS configuration file (parameter jdbc.MaxRows). See ORDS documentation for details.

Another fun fact: by using apex_json package size of JSON object was 32.1 KB. In all other examples size was 29.1 KB. I suppose it's because of unnecessary blank lines/characters.

After that I've compared average speed to generate JSON object. The fastest way was to generate it manually in Example 5 (by concatenating strings and by using sys.htp.prn), but I would recommend to use apex_json package. There are many reasons why and I won't explain them now.

When I got my JSON back I wondered what can I do with it in APEX except manually parsing it in jQuery. So I've googled about client side data binding methods implemented in JavaScript and found out Mustache.js.
What is Mustache.js and why I've choose it? It's JavaScript templating engine it's lightweight (min version around 10KB) and simple and you must agree that the name and logo are great!

I won't go into details how to use it. You have everything very well documented here. Maybe I'll cover this in some future post.

So, what did I use it for? It's simple - for templating. :)
I've picked up classic report template from Universal Theme added up Mustache tokens and voilà - I had same report as if it's generated by classic report region but it seemed really fast. Then I thought I should compare execution time and size with classic report region.

I've opened up my Console Window and saw interesting stuff. The size of classic report response (after region refresh) was 122KB and it took about 380ms (in average) to get it (the fastest way with JSON was around 100ms). It was more than 3x slower than JSON generation and the size of object was more than 4x bigger. Isn't this amazing? Should APEX in some future release use JSON and some templating engine for similar stuff?

By then, you can use it in your apps immediately. There are numerus ways, so be creative (see Choose Template option in example)!

For more, visit my presentations at SOIUG 2015 and HROUG 2015 in two weeks.

I hope I'll have more time to write more posts about JSON and Mustache.js soon.

Once again, you can see demo here.

*I've tested execution time and JSON size locally on APEX and Oracle XE