Friday, August 17, 2018

APEX 18.1: Fix Interactive Grid JS error in translated apps

Recently one of our clients upgraded APEX from 5.1 to 18.1 and all Interactive Grid regions/pages were instantly "broken". There was some strange JS error (" few arguments") in the console window.

After some investigation I found out the cause of the error. It's actually reported as a known issue (28202781 - INTERACTIVE GRID IN TRANSLATED APP GIVES ERROR ON UPGRADE). Apparently the text string APEX.IG.SUMMARY was changed in APEX 18.1 to have fewer parameters and if you have translated application it will raise a JS error at runtime. I don't know why APEX raises error in this case and I hope that this will be fixed in some next release, but for now here's the way how you can fix this.

Of course, you can go to APEX builder > Shared Components > Text Messages and translate it there with the new text message that will have exactly two parameters (if you have less than two you'll again get JS error). Original text is Interactive Grid. Report: %0, View: %1.

You can also do it with PL/SQL script:
  FOR i IN (SELECT workspace_id
              FROM apex_workspaces
             WHERE workspace = :WORKSPACE_NAME)
              FROM apex_application_translations
             WHERE application_id = :APP_ID
               AND translatable_message = 'APEX.IG.SUMMARY')
          p_id           => i.translation_entry_id,
          p_message_text => 'Interactive Grid. Report: %0, View: %1');

Tested with APEX


Wednesday, June 27, 2018

APEX 18.1: No need for configuration tables any more

There's probably no application that doesn't need some kind of configuration table for storing different data across different environments (development/test/production) - like URL to the report server, configuration parameters and stuff like that.

Before APEX 18.1 in most cases I would create some simple key-value configuration table, set procedure and get function...repeating same task over and over again.

From APEX 18.1 you don't have to do that anymore. There's new feature called Application Settings.

There are two ways to manage Application Settings. One is from the Application Builder (Shared Components):

You can define several properties there, but the one I like the most is On Upgrade Keep Value. If set to Yes it will keep the setting value upon application upgrade. That means that you can export your application from the development environment and import it to the other environments without any worries that parameter values will be overridden.

There's also API to control those parameters from PL/SQL. There's new package apex_app_setting with set_value procedure and get_value function. For more info see documentation.


Friday, June 8, 2018

APEX 18.1: Generate URL checksum outside APEX session

As a follow up to my previous blog post about APEX sessions, there's one more thing that you can do now (in APEX 18.1) by using apex_session package - generate checksum for URLs created outside APEX session - for example in some email notifications created in a DB job.

First of all you need to enable Deep Linking (go to the Application Properties > Security > Session Management and set Deep Linking to Enabled). You can learn more about deep linking in the documentation.

Also, you have to enable Session State Protection for the item that you want to set in URL (in my case P2_DEPTNO):

Before generating URL you have to create session:
As I said in my previous blog post, to use any of the procedures from the apex_session package you have to run them as application parsing schema, one of schemas assigned to the workspace or one of the users with APEX_ADMINISTRATOR_ROLE role.

After that you should be able to create URL with the checksum by calling apex_page.get_url function:
MGORICKI@db12c.local> begin
  2    dbms_output.put_line(
  3      apex_page.get_url (
  4      p_application => 105,
  5      p_page        => 2,
  6      p_items       => 'P2_DEPTNO',
  7      p_values      => '10') 
  8    );
  9  end;
 10  /

MGORICKI@db12c.local> exec apex_session.create_session(105,1,'MGORICKI');
PL/SQL procedure successfully completed.

MGORICKI@db12c.local> begin
  2    dbms_output.put_line(
  3      apex_page.get_url (
  4      p_application => 105,
  5      p_page        => 2,
  6      p_items       => 'P2_DEPTNO',
  7      p_values      => '10') 
  8    );
  9  end;
 10  /


Thursday, June 7, 2018

APEX 18.1: Debugging of the APEX sessions was never easier

I've already blogged about apex_session package and the debugging of the APEX sessions in version 5.1. Same package got bigger in 18.1 with 4 new great procedures:
Those procedures made debugging of the APEX sessions even easier. The same was possible before by using OraOpenSource's OOS_UTIL_APEX package, but it's somehow nicer when you can use native functionality.

With this procedures you can create or join existing session from your favorite IDE and use/debug all those session dependent features like:
  • collections
  • item session state
  • apex_mail
  • ...
To use any of the procedures you have to run them as application parsing schema, one of schemas assigned to the workspace or one of the users with APEX_ADMINISTRATOR_ROLE role.

To create session run following command:
After that, if you run following SQL statement:
select v('APP_ID') as app_id
     , v('APP_SESSION') as app_session
     , v('APP_USER') as app_user 
  from dual;
you'll see that all necessary APEX variables are set:
MGORICKI@db12c.local> select v('APP_ID') as app_id, v('APP_SESSION') as app_Session, v('APP_USER') as app_user from dual;

MGORICKI@db12c.local> exec apex_session.create_session(100,1,'MGORICKI');

PL/SQL procedure successfully completed.

MGORICKI@db12c.local> select v('APP_ID') as app_id, v('APP_SESSION') as app_Session, v('APP_USER') as app_user from dual;
100     2847775298572  MGORICKI
It's similar if you want to join existing session. You just have to copy session ID (3rd parameter from APEX URL) of the runtime app and add it to the apex_session.attach call:
  apex_session.attach (
    p_app_id     => 100,
    p_page_id    => 1,
    p_session_id => 837851762118
After that you can get/set item session state, query/create/modify collection and a lot more...

Set/Get Item Session State

Lets say you have v function call inside of a view or a procedure (I don't encourage you to do that, it's better to use bind variables or input parameters). Now, you can easily debug it from IDE by setting item session state:
MGORICKI@db12c.local> create or replace view v_emp_filtered 
  2  as 
  3    select * 
  4      from emp 
  5     where deptno = v('P1_DEPTNO');

View V_EMP_FILTERED created.

MGORICKI@db12c.local> select * from v_emp_filtered;

no rows selected
MGORICKI@db12c.local> exec apex_util.set_session_state('P1_DEPTNO', 10);

PL/SQL procedure successfully completed.

MGORICKI@db12c.local> select * from v_emp_filtered;
7839   KING    PRESIDENT        17-NOV-81  5000  10    10      
7782   CLARK   MANAGER    7839  09-JUN-81  2450        10      
7934   MILLER  CLERK      7782  23-JAN-82  1300        10      

Create/Query Collections

Same goes for the collections. You can now query/create/modify them from IDE:
MGORICKI@db12c.local> select seq_id, c001 as empno, c002 as ename, c006 as sal from apex_collections where collection_name = 'EMP';

no rows selected

MGORICKI@db12c.local> exec apex_collection.create_collection_from_query('EMP', 'select * from emp');
PL/SQL procedure successfully completed.

MGORICKI@db12c.local> select seq_id, c001 as empno, c002 as ename, c006 as sal from apex_collections where collection_name = 'EMP';
1       7839   KING    5000  
2       7698   BLAKE   2850  
3       7782   CLARK   2450  
4       7566   JONES   2975  
5       7788   SCOTT   3000  
6       7902   FORD    3000  
7       7369   SMITH   800   
8       7499   ALLEN   1600  
9       7521   WARD    1250  
10      7654   MARTIN  1250  
11      7844   TURNER  1500  
12      7876   ADAMS   1100  
13      7900   JAMES   950   
14      7934   MILLER  1300  

14 rows selected.
To reset your IDE environment use delete or detach procedures.

You can find more about the apex_session package in documentation.


Tuesday, December 12, 2017

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 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:
But that breaks sticky headers when you scroll down. To fix that I had to use different CSS:
If you want to use other regions for master-detail pages take a look at my plugin on

Demo is available here.

Tested on (v5.


Wednesday, November 29, 2017

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.


In version 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 {

Demo is available here.


Friday, August 4, 2017

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:

  apex_session.set_debug(p_session_id => &SESSION, p_level => 4); 
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


Tested on APEX