Generate APEX URL in JavaScript

When developers want to generate APEX URL from a JavaScript the thing that most of them will do is to concatenate URL string like this:

But there's one (unfortunately) undocumented feature that you can use to make it easier. It's possible to do it by using apex.util.makeApplicationUrl function that excepts a JavaScript object as the only parameter. In that object, you can define all that is needed to generate an APEX URL.

Here is an example:

Note: the function will not generate the checksum for pages or items with session state protection turned on.

Enjoy!

Tested in APEX 19.1.0.00.15

ODTUG KSCOPE 2016...

...was the place to be, at least if you are PL/SQL or APEX developer. Numbers say it all: 350+ presentations, 50+ APEX presentations, Sunday symposium, open mic night, Thursday's deep dive session and many more - all of that in only 4 and a half days. Amazing!

One thing was special there - the APEX community. It look like all those 8 years that I'm working with APEX where gathered in one place - APEX dev team and all popular presenters, bloggers and tweeters that you can find out there when you google about APEX and all of them so kind, friendly and eager to share knowledge.

Also, it was great to see that all those rooms with APEX presentations (at least the ones that I've attended) where so full. I don't have anything against ADF, but it was so sad to see 5-10 people on their presentations while APEX rooms where so packed. It reminded me of many projects in which we needed to prove that APEX is the next step when you want to get rid off Oracle Forms. If only I had that picture then. :)

And the content? It wasn't all about the APEX 5.1 as I've expected. It was on the first day on the Sunday symposium when APEX team talked about the new features in 5.1, but that was only a great introduction for the rest of the week. The only problem was that you have to choose only one presentation per session and sometimes it was really hard. There were 3 to 4 parallel APEX presentations + PL/SQL + ORDS and always something interesting.

I had my presentation on Tuesday at 8:30am, first session of the day. I got some great feedbacks so I suppose it went well. If you want to take a look at the slides you can find them here and you are free to contact me anytime if you need some additional information or explanation.

On the last day we had this deep dive sessions that I've really liked. I liked the content and concept because it was a two hour session divided in 10 minute presentations by some of the best and well known APEX speakers. Short and interesting...and there was a hidden gem from Joel, a feature long awaited, about session isolation across multiple browser tabs. From my perspective one of top features in APEX 5.1.

During KSCOPE I wrote some notes on stuff that I want to explore a bit more so if you want to take a look:

• Electron.js - extending your APEX apps to desktop
• Camunda - workflow solutions for APEX apps
• FlowControl - PL/SQL Open Source workflow solution
• Nightmarejs - high-level automation library
• Service Workers - a method that enables applications to take advantage of persistent background processing, including hooks to enable bootstrapping of web applications while offline.

And don't forget to take a look at OraOpenSource project. It was mentioned so many times there. Great project with really nice free products that can be very useful in every APEX app.

To conclude, it's time to plan KSCOPE2017, you'll not regret it.

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:

begin
  apex_util.set_security_group_id(apex_util.find_security_group_id(:WS_NAME));
end;



Tested on APEX 5.0.3.00.03

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.

Remove Annoying Login Message

If you are annoyed by login message "...Please wait 5 seconds to login again..." that apears in APEX 4.2.* after failed login attempt you're lucky for reading this post. There is an easy way to switch it off.

Login to APEX Builder like instance admin and go to Manage Instance > Security option. Find General Login Control region and set property Delay after failed login attempts in Seconds to 0.

Tested with APEX 4.2.5.00.08 and Oracle XE 11.2.0.2.0 database.

View Item Session State from PL/SQL IDE

One and a half year ago I had a post about viewing data from APEX collections from your favorite PL/SQL IDE (PL/SQL Developer, TOAD, SQL Developer...). Similar to that you can view current item session state. First step should be getting grants from some internal APEX packages (executing as sys user):

grant execute on apex_040200.wwv_flow_security to test;
grant execute on apex_040200.wwv_flow_session_state to test;

After that you have to register application and session ID in your PL/SQL IDE (running in parsing schema, e.g. test):

begin
   -- Set Application ID       
   apex_040200.wwv_flow_security.g_flow_id := 121;
      
   -- Set Session ID  
   apex_040200.wwv_flow_security.g_instance := 802090201361;   
end; 

Any time you want to see current session state from some item you have to refresh current session state values by executing:

begin   
   -- Refresh Item Session State Values
   apex_040200.wwv_flow_session_state.fetch_into_substitution_cache(true);
end;

After that you can see value from any item in current session using function v or apex_util.get_session_state, for example:

select v('P1_TEST') from dual

Tested with APEX 4.2.4.00.08 and Oracle XE 11.2.0.2.0 database.

NOTE: It wouldn't be a good idea to give these grants on production instances. :)

APEX helps ORACLE TEAM USA to win the America's Cup

Blog post by David Peak about using APEX to help ORACLE TEAM USA to win 34rd America's Cup:
http://tinyurl.com/klk2n86

Amazing!

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. 

View data from APEX collections in PL/SQL IDE

If you ever wondered, there's an easy way to see data from APEX collections in your favorite PL/SQL IDE (PL/SQL Developer, TOAD, SQL Developer...).

You only have to run short anonymous PL/SQL block and define:

• workspace name (workspace ID)
• application ID
• session ID.

Here's the script:

declare

  v_workspace_id apex_workspaces.workspace_id%type;

begin

  select workspace_id

    into v_workspace_id

    from apex_workspaces

   where workspace = '&WORKSPACE_NAME';

   

   -- Set Workspace ID

   apex_util.set_security_group_id(v_workspace_id);

   -- Set Application ID

   apex_application.g_flow_id  := &APP_ID;     

   

   -- Set Session ID

   apex_application.g_instance := &APP_SESSION;  

end;

After you run this script you can easily do query from apex_collections view and you'll see the result.

Dynamically changing item labels

Today I’ve got one idea how to simply change (in my example translate) item label dynamically with value from database using shortcuts.

First of all you have to create database table to store translated labels, associated to item name and language. For example:

  create table item_labels (
      item_name varchar2(255)
    , item_label varchar2(4000) 
    , lang varchar2(10));

After that you have to create function for fetching label for specific item and current language (that can be set in some application item):

  create or replace function get_item_label (p_item_name varchar2

                                           , p_lang      varchar2 default 'EN')

    return varchar2

  is

    v_item_label item_labels.item_label%type;

  begin

      select item_label

        into v_item_label

        from item_labels

       where upper(item_name) =  upper(p_item_name)

         and upper(lang)      = upper(p_lang);

     return v_item_label;

  exception

    when no_data_found then

      select pi.label

        into v_item_label

        from apex_application_page_items pi

       where pi.application_id = v('APP_ID')

         and pi.page_id = v('APP_PAGE_ID')

         and pi.item_name = p_item_name;

      return v_item_label;

  end get_item_label;

To dynamically change item label you have to modify definition of item label template to hide default label and to show one from database (using TRANSLATE_LABEL shortcut).

And the last step should be creation of shortcut (in my example named TRANSLATE_LABEL) that for source has PL/SQL function body that returns translated label:

At the end just add data to item_labels table and apply item label template. Don’t forget to create application item CURRENT_LANGUAGE that can be set dynamically.

@Kirsten:
For item label with help definition should look like this: