Region Display Selector - How to Prevent Scroll on Page Load

A few days ago I visited my dear friends Aljaž Mali and Andrej Grlica and they had one interesting case. They had a page where they used region display selector in Scroll Window mode to display some regions in tabs. Since region display selector wasn't positioned on top of the page, on page load navigation was scrolled down to the first tab region (as in this example).

To fix it, the first thought from Andrej and me was to somehow do it with JavaScript, but after we examined JS API we concluded that there isn't any clean solution.

And then, as always, four eyes are better than two...we came out with a nice but dirty solution.

The Fix

To fix it you need to:

• create a dummy region with Region Display Property turned on an put it before all other regions with that property on
• use region template - Blank with Attributes 
• add Static ID (in our case it was noScrollTab)
• add the following CSS to the page either as inline CSS or to the external static file (preferred way)

How?

What have we done here? We created a region on the first position in region display selector - the region that usually is scrolled down to on page load and then with CSS we've positioned it to the top left corner of the page and hid it.

The demo is available here.

Tested in APEX 19.2.0.00.18.

Enjoy!

Interactive Grid: How to Fix Blank Toolbar DIV

One of the best functionalities of the Interactive Grid is declarative master-detail option. That's the reason why I like to use them also in readonly mode. Most of the time I don't need toolbar in detail regions so I turn it off (Region Attributes > Toolbar > Show = No).

But, currently (in APEX 5.1.4.00.04 and less) there's little bug that I can't ignore. It's 9px blank div above report headers:

At first I thought that's easy - I'll just use :empty CSS pseudo selector and hide DIV element with CSS class .a-IG-header:

.a-IG-header:empty{
  display:none
}

But that breaks sticky headers when you scroll down. To fix that I had to use different CSS:

.a-IG-header:empty{
  padding:0;
  border-bottom:0
}

If you want to use other regions for master-detail pages take a look at my plugin on apex.world.

Demo is available here.

Tested on apex.oracle.com (v5.1.4.00.04).

Enjoy!

Pimp Your Forms - Post/Pre Text Icons

I saw this at the presentation from Patrick Wolf (I believe somewhere in 2016.). You may already know this but today it took me some time to remember how it works.

There's an easy way to make your forms look nicer by using declarative option to display pre/post item property values as a part of an item (Display as Block). You can display icons, text or links there.

How To

To enable this, go to the Template Options of the item and set property Item Pre/Post Text to Display as Block:

After that, put the following HTML code in Pre/Post Text item property (in this case it displays Euro currency icon):

You can use any of the icons from Font APEX library there.

Note

In version 5.1.3.00.5 there's a CSS bug with icon alignment. You can fix this by adding following CSS (preferable to global CSS file or Theme Roller):

.t-Form-itemText .fa {
  vertical-align:middle;
  top:-1px;
}

Demo is available here.

Enjoy!

How to debug/trace APEX session

In most cases you don't have access to production environment and reproducing of a production bug can be a real problem.

If you don't use logger (which I strongly recommend) or you don't have a feedback page you can always use APEX's native debugging tool.

There's a easy way to use native APEX debug and you can turn it on:

• with 5th URL parameter that can be set to YES or to some value between LEVEL0 to LEVEL9 (there's no LEVEL3 and LEVEL7). YES equals LEVEL4 (more on debug levels here and here). You can use this only for applications that have debugging property turned on (under application properties). 
• programatically with apex_debug package.

But on production environment you probably want to track a specific user session. To turn it on for specific user session you can use apex_session package (new from APEX 5.1).

To find out what session you want to track you can ask your end user to read you a third parameter in URL (don't do that 🙂) right after application and page id or you can track it yourself by querying APEX dictionary view apex_wokspace_sessions:

select *
  from apex_workspace_sessions 
 where workspace_name = &WORKSPACE_NAME 
   and user_name = &USERNAME;

After that you can call apex_session.set_debug to turn on debugging for this specific user session:

begin 
  apex_session.set_debug(p_session_id => &SESSION, p_level => 4); 
  commit; 
end;
/

Other solution is to use APEX builder (if you have access). To turn it on in APEX builder go to the Monitor Activity > Active Sessions find the session and change session attribute Debug Level

Then you can ask your end user to try to reproduce an application bug and after that you can query apex_debug_messages dictionary view to check for errors or you can use APEX Builder to do the same (Application > Utilities > Debug Messages).

You can use same package (apex_session) to turn on SQL tracing (procedure set_trace). Tracing can be turned on also with URL parameter. 

Enjoy!

Tested on APEX 5.1.2.00.09

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.

Enjoy!

Tested in APEX 5.0.3.00.03

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

Example

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.

Solution

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 5.0.3.00.03

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-header{
    box-shadow:none
  }
  .a-IRR-table td{
    border-left:1px solid transparent
  }

*Tested on APEX 5.0.1.00.06.

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...).

Speed Up Your APEX apps with JSON and Mustache.js

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

First of all I found some great blog posts from Dimitri Gielis:

• Generate nested JSON from SQL with ORDS and APEX 5
• JSON for APEX Developers (part 1)
• JSON for APEX Developers (part 2)

and Dan McGhan:

• Relational to JSON with ORDS
• Relational to JSON with APEX_JSON
• Relational to JSON with PL/JSON
• Relational to JSON with Node.js

where you can read almost everything that you'll ever need to know about APEX and JSON.

After that I've decided to try it by myself. So I've created a simple examples of generating JSON from same source (query from all_objects table) with the 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:

1 2 3 4 5 6

SELECT rownum x , owner a , object_name b , object_type c FROM all_objects WHERE rownum <= 500

Why I was using short aliases in the query, like x, a,b,c? Because size of a 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 a 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 5.0.1.00.06 and Oracle XE 11.2.0.2.0

APEX 5: Sticky Regions

You know that in APEX 5 you can (for IR) define how you would like to display report headings

• Fixed to Region
• Fixed to Page
• None

By using same API (not documented) you can make your regions "sticky".
First, you have to set Static Region ID (for example rgnSticky) of your sticky region.

After that add onload JS code:
$("#rgnSticky").stickyWidget({toggleWidth:true});

Check out demo here.

*Tested on APEX 5.0.1.00.06.

APEX 5: Add Spinner to Classic Reports Plugin

I've created a little APEX5 Dynamic Action plugin that adds spinner on dynamic refresh of classic report regions (like you have for Interactive Reports).

You can see info and download it here: https://goo.gl/84ITaV

APEX 5: Deprecated and Desupported Features and Changed Behavior

I've started to read APEX 5 BETA documentation (Beta Draft: 2015-01-16) Release Notes and I've found out two interesting chapters -  Deprecated and Desupported Features and Changed Behavior.

In chapter named Deprecated and Desupported Features you can find many interesting things about APEX 5 and what should you avoid to use in your future APEX 5 applications. 

Here are some things that caught my eye more than others:

• Deprecated column types in Classic Report. Oracle recommends to use Tabular Forms or APEX_ITEM API for creating editable reports.
• File Browse Item should use APEX_APPLICATION_TEMP_FILES table for select, update or delete operations instead of WWV_FLOW_FILES. Deletes are no longer necessary, as the file will automatically be purged after the request or when the session is purged.
• If you used WWV_FLOW_FILES as a permanent store, the files should be copied into their own BLOB column(s) within a table within your schema.
• In APEX 5.0 there is only one button type which can be positioned in a region or next to items. Finally, no more Item Buttons! :)
• Dynamic actions based on DOM Objects have been deprecated. You should use jQuery Selector or JavaScript Expression instead.
• Page computations and validations based on SQL expressions have been deprecated. You should use PL/SQL expressions or PL/SQL Function returning VARCHAR2 types
• #PLUGIN_PREFIX# substitution string is not supported any more in plug-in files. You should use relative URLs instead.

Also, there's a nice chapter about Changed Behavior. Here are some nice bullets to remember:

• Static files will be stored in central repository that is available for a specific application (Static Application Files) or for all applications within a workspace (Workspace Application Files)
• Static application files uploaded to Shared Components are automatically included in an application export. No more recreating supporting object scripts. :)
• In a future version of Oracle Application Express, the dictionary view APEX_WORKSPACE_FILES will no longer include CSS, image or static files uploaded in Shared Components. You should use the new views APEX_WORKSPACE_STATIC_FILES and APEX_APPLICATION_STATIC_FILES instead.
• Because of internal changes to the views APEX_APPLICATION_FILES and WWV_FLOW_FILES, it is not possible anymore to lock rows with SELECT FOR UPDATE

Please check the whole documentation for details. Remember, for now this is only BETA documentation. 

Don't forget to check those chapters in full documentation before moving to APEX 5.

APEX 5: jQuery Migration

If you like to write custom JS code in your APEX applications or to use lots of 3^rd party plugins you should be aware of changes in jQuery and JS libraries in APEX 5.

APEX 5 (at least current version 5.0.0.00.28) uses jQuery 2.1.3 and if you plan to migrate from APEX 4.2 you should check for changes from jQuery version 1.7.1 which is included in APEX 4.2. There are some things that are deprecated and removed like .die() or .live() event handlers.

Here you can find things that are removed:

http://api.jquery.com/category/removed/

But APEX development team was one step ahead and they provided us with quick solution to include jQuery Migrate plugin that restores deprecated features and behaviors of jQuery.

You can turn it on under the User Interface Attributes Page, JavaScript tab. My advice is that you should clean up your code and not to use any deprecated code.

Note that, as you can read in jQuery documentation, jQuery 2.x has the same API as jQuery 1.x, but does not support Internet Explorer 6, 7, or 8. APEX development team also thought of that and they put conditional comment to handle this:

<!--[if lt IE 9]><script type="text/javascript" src="/i/libraries/jquery/1.11.2/jquery-1.11.2.min.js?v=5.0.0.00.28"></script><![endif]-->

<!--[if gte IE 9]><!--><script type="text/javascript" src="/i/libraries/jquery/2.1.3/jquery-2.1.3.min.js?v=5.0.0.00.28"></script><!--<![endif]-->

Also be aware that by default when you create new application in APEX 5 there is no more old non-namespaced APEX JavaScript functions in your application (as part of legacy.js). You can include them by turning on property Include Legacy Javascript in User Interface Attributes page, JavaScript tab.

Again, don't do that. Rather clean up your code!

On Demand Process Bug

In my last post I wrote about security of on demand application processes. While I was testing this I've noticed a bug in APEX version 4.2.6.00.03 and APEX 5 EA2 (probably in some older versions too).

If you have on demand application process (without authorization scheme) and call it from public page (e.g. login page 101) you'll get additional unwanted data in response message.

For example, if you create public on demand application process like this that outputs some string like"OK":

and call it in some on load dynamic action on public page (like login page):

When you take a look at console window you'll see additional data with "OK" string:

If you are expecting that your message is returning something in JSON format you'll get an JS error on page.

I've reported a bug to APEX team and they've fix it for version 5.

Secure your On Demand processes

By default when you create On Demand application process there is no authorization scheme selected:

and that makes your On Demand processes very vulnerable and publicly available.

For example, if you create On Demand application process without authorization scheme named DUMMY that creates record in DEPT table:

it can easily be called from browser console window without being logged in into application (e.g. on login page 101) by executing this JS statement:

apex.server.process ("DUMMY" 
                    ,{x01: "IT",x02: "LOS ANGELES"}
                    ,{dataType:"text"}
                    );

Table before executed JS statement:

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

Table after executed JS statement:

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON
50	IT	LOS ANGELES

If you change authorization scheme of your On Demand application process to Must Not Be Public User:

it will not be publicly available and unauthorized users will not be able to call it without logging in into application.

Environment: APEX 4.2.5.00.08 and Oracle XE 11.2.0.2.0 database.

APEX static files bug

I noticed a bug regarding uploading static files in APEX. When you try to upload file with same name as existing one you'll get error message that static file name already exists. But if you query apex_application_files view you can see that this file was uploaded (like workspace file). Funny fact is that when you go to static files page in APEX builder you won't see that "hidden" file.

This can lead to problems with static files in runtime because APEX sometimes uses some of the "hidden" files and not the ones you see in APEX builder. To clean up this mess you should delete files manually.

One way to do it is to use APEX SQL Workshop, but it's also easy to do it from your favorite PL/SQL IDE. The only thing you have to do is to set workspace id (security_group_id).

declare

    v_workspace_id apex_workspaces.workspace_id%type;

begin

  select workspace_id

    into v_workspace_id

    from apex_workspaces w

   where workspace = upper(&p_workspace_name);

    

  apex_util.set_security_group_id(v_workspace_id);

end;

/

After that you can manage your static files in current session of IDE by using view apex_applicaton_files (or wwv_flow_files).

This was tested in APEX 4.2.2.00.11, but I know that same problem existed in earlier versions of APEX.

Saving Values of Disabled Items to Session State

As you may know disabled items are not submitted in HTML forms (see w3schools.com). It's same for APEX items, at least for ones disabled with dynamic actions or JavaScript/jQuery.

There's a simple workaround for this. You can create dynamic action on event Before Page Submit that executes JavaScript code that will remove HTML attribute disabled from all items on page:

$(':disabled').removeAttr('disabled');

If you want this to work on all pages, put this dynamic action on global page (page 0).

As I said, this will not work for text field items that have property Disabled set to Yes and Save Session State to No. To save this kind of text field items set property Save Session State to Yes. In this case input item will have HTML attribute readonly, instead of disabled.

APEX debug levels

From time to time I run into some new stuff about APEX. So few days ago I was investigating something about debug messages and figured it out that there are different levels of debug messages in APEX.

You probably know that as a sixth argument in APEX URL you can pass either YES or NO and that will turn on/off page debugging (if it's enabled in application properties). So the thing I discovered is that you can also pass LEVELn where n is between 1 and 9 and stands for level of debug detail to display. The value of YES is equal to LEVEL4.

If you take a look at apex_debug package specification you can see meaning of each level (although missing levels 3 and 7):

-- critical error
c_log_level_error        constant t_log_level := 1; 

-- less critical error
c_log_level_warn         constant t_log_level := 2; 

-- default level if debugging is enabled (e.g. used by apex_application.debug)
c_log_level_info         constant t_log_level := 4; 

-- application: messages when procedures/functions are entered
c_log_level_app_enter    constant t_log_level := 5; 

-- application: other messages within procedures/functions
c_log_level_app_trace    constant t_log_level := 6; 

-- apex engine: messages when procedures/functions are entered
c_log_level_engine_enter constant t_log_level := 8; 

-- apex engine: other messages within procedures/functions
c_log_level_engine_trace constant t_log_level := 9; 

You can set this level when adding debug messages manually using apex_debug.message procedure.

Condition Type – Request Is Contained within Expression

I've just remembered the issue that I've had few months ago with unexpected (at least for me) behavior of condition type Request Is Contained within Expression. A real headache to debug and figure out.

At first glance, when I saw this condition type, I’ve expected that requests must be in Expression 1, comma delimited, and that there must be an exact match on condition evaluation. But that’s not the case.

For example, if you have three buttons with requests CREATE, CREATE_ANOTHER and CREATE_AND_RETURN, and process condition with condition type Request Is Contained within Expression where Expression 1 equals CREATE_ANOTHER,CREATE_AND_RETURN:

the process will be executed no matter which button you press, even the one with request CREATE, because condition is evaluated as true when request string is contained within Expression 1 (without exact match).

So to avoid such behavior I always use condition type PL/SQL Expression with condition “:REQUEST in (...)”, for example:

Crazy little thing called dialog...

..today cost me a lot of nerves. I had a simple form and after every page submit, session state value of items were sequentially shifted (after page submit, value of first item become value of second item, value of second item become value of third item and so on...).

Initial form state

Form state after first submit

Form state after third submit

The first time after many years working with APEX I’ve got really pissed off. I’ve tried to isolate the problem, so I’ve deleted most of page items and had a form with only two items, with no processes, branches, dynamic actions...and it happened again – after submit, item values sequentially shifted. Then I’ve tried to create new page and all worked fine.

No matter what, I wanted to discover what’s the problem on the old page. After some time I got to POST request and found a clue. The items were submitted, but the sequence wasn’t right. I’ve got items posted in order from p_t02 to p_t03 and the p_t01 was missing. So I’ve opened FireBug and found it just before </BODY> HTML tag in jQuery UI Dialog. And the answer was here!

POST request

On page 0 there was a region (with “missing” item – p_t01) used for jQuery Dialog. After initialization of jQuery dialog, dialogs HTML was generated just before </BODY> HTML tag and the dialog region was pulled from its original position (inside <form id=” wwvFlowForm “> HTML tag) and moved just before the end of HTML body. As you may guess, only items inside <FORM> element are submitted to session state.

Quick solution of this problem was to put regions used for jQuery dialogs as last regions on page, so its items will get the last p_tXX values for name HTML attribute.

You can find test example here.

I hope that this post will help someone someday! :)

Edit on July 29, 2015:
More about this problem and order of submited items you can find here.