Interactive Grids button’s customization

One of the features I always hated in the previous versions of APEX (before 5) were tabular forms. I never got then, they were always behaving funky, it was hard to debug and I preferred to write dynamic tabular forms by myself then with the built in functionality. It changed with the release of Interactive Grids with the Oracle APEX version 5.

When I’ve started working with them it was in the English speaking company and I didn’t face the problem I had later in Czech company, where I wanted to customize or translate buttons. It was not difficult to understand words like “edit” or “save” for users, but I wanted to have consistency in these buttons across the whole application.

The first button I wanted to change was “add new row”. Luckily to change this label it’s pretty straightforward as it has its own setting in the Interactive Grid Attributes – Toolbar menu, as shown on the picture below.

Sadly this is the extent of the customization you have about the buttons. If you want to add icon, change the button to “hot” or change the names of the other buttons, you simply have to dive into the Interactive Grid JavaScript model. I’ll maybe dig more into details later, but for now, I’ll just show you the pieces, which are related to the topic today. There are few settings you can adjust for each button.

button.iconOnly = true / false;
button.hot = true / false;
button.label = "label"
button.title = "tooltip"
button.iconBeforeLabel = true / false;

I believe all the settings are self explanatory. The only thing which remains, is how to use this in the real example. If you have a look into attributes of Interactive Grid region, you can see, it has field for something called JavaScript Initialization Code (in advanced section of attributes). There you can adjust the configuration of the Interactive Grid region. First we copy the default toolbar, get all the buttons we want to edit and setup their properties, like icons, labels and icon position as shown below. Lastly you save the configuration of your toolbar into the configuration of your Interactive Grid and return the config to be processed.

function(config) {
    let $             = apex.jQuery,
        toolbarData   = $.apex.interactiveGrid.copyDefaultToolbar(),
        addrowAction  = toolbarData.toolbarFind("selection-add-row"),
        saveAction    = toolbarData.toolbarFind("save"),
        editAction    = toolbarData.toolbarFind("edit");
     
    addrowAction.icon = "icon-ig-add-row";
    addrowAction.iconBeforeLabel = true;
    addrowAction.hot = true;
    saveAction.label = "Uložit změny";
    saveAction.iconBeforeLabel = true;
    saveAction.icon ="icon-ig-save-as";
    saveAction.hot = true;
    editAction.label = "Editovat";

    config.toolbarData = toolbarData;
    return config;
}

After applying the settings and refreshing the page, you can see the results of your labor.

thanks to https://www.stinolez.com/2018/10/19/interactive-grids-buttons-customization/

APEX: Page Access Protection and Session State Protection

APEX’s Page Access Protection (PAP – For Pages) and Session State Protection (SSP – For Items) are excellent security tools to help prevent users from altering session values. What some people may not be aware of is that if you enable PAP for page it does not prevent users from altering the session state of items on that page. All it does is require that any items passed through that page via the URL require a checksum. Malicious users can still alter the item’s session state using AJAX or from other pages. Long story short, if you want to lock your application down you need to enable SSP for all required items.

APEX has a great tool to do this quickly for you rather than having to go into each page item. Shared Components / Session State Protection / Page / (click page number). You can now set the PAP and the SSP for all the page items.

If you do use PAP and SSP the following queries will help you do some quick validations to ensure all your security checks are in place

Pages without Page Access Protection

SELECT aap.application_id,
       aap.application_name,
       aap.page_id,
       aap.page_name
  FROM apex_application_pages aap
 WHERE LOWER (aap.page_access_protection) = 'unrestricted'
   AND aap.application_id = :app_id

Page items without Session State Protection

SELECT aapi.application_id,
       aapi.application_name,
       aapi.page_id,
       aapi.page_name,
       aapi.item_name
  FROM apex_application_page_items aapi
 WHERE aapi.application_id = :app_id
   AND LOWER (aapi.item_protection_level) = 'unrestricted'

Pages which have Page Access Protection, but have page items with no Session State Protection

This query helps identify pages which you think are locked down, but end users could set the session state of item values

SELECT aapi.application_id,
       aapi.application_name,
       aapi.page_id,
       aapi.page_name,
       aapi.item_name
  FROM apex_application_pages aap,
       apex_application_page_items aapi
 WHERE LOWER (aap.page_access_protection) != 'unrestricted'
   AND aap.application_id = :app_id
   AND aapi.application_id = aap.application_id
   AND aap.page_id = aapi.page_id
   AND LOWER (aapi.item_protection_level) = 'unrestricted'

Errors when downloading a file on page submit in Oracle Application Express 5.1 or later..

Errors when downloading a file on page submit in Oracle Application Express 5.1 or later…

Error: SyntaxError: Unexpected token < in JSON at position 0

Recently, Sharon Kennedy from our team approached me for some help with file download in Oracle Application Express (APEX).  Sharon is the primary developer of Oracle Live SQL (among many of her other responsibilities), and she wanted to initiate a file download in a page process, after page submission.  Since I’ve done this 100 times in APEX applications, should be easy, right?

Back in 2014, I wrote a short blog post showing how to generate a link to download a file from a BLOB stored in a table.  But this problem was slightly different.  The application flow was:

  1. In Oracle Live SQL Administration, an administrator would click the button “Download Oracle Content”
  2. The page would then be submitted, and a PL/SQL page process would fire, which would query all of the static scripts and tutorials from Live SQL, zip them up using APEX_ZIP, and initiate a file download.

However, when the button was clicked, the page would be submitted, no file download would be initiated, and the following error was displayed on the page:

Error: SyntaxError: Unexpected token r in JSON at position 0

After spending more than an hour debugging the Live SQL application, I resorted to a simpler test case.  I created a trivial application with a button on the first page, which would submit and invoke the PL/SQL page process:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare
    l_file_blob blob; 
    l_file_name apex_application_files.filename%type; 
    l_file_mimetype apex_application_files.mime_type%type; 
begin
    select blob_content, mime_type, filename into l_file_blob , l_file_mimetype , l_file_name from apex_application_files where id = 2928972027711464812;    
    sys.owa_util.mime_header( l_file_mimetype , false ); 
    sys.htp.p('Content-Disposition: attachment; filename="' || l_file_name ||'"'); 
    sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_file_blob )); 
    sys.owa_util.http_header_close; 
    sys.wpg_docload.download_file( l_file_blob ); 
    -- Stop page processing 
    apex_application.stop_apex_engine ; 
end

With my test case, it was exactly the same error encountered, the meaningless error message of “Error: SyntaxError: Unexpected token r in JSON at position 0”.

I finally gave up and contacted Patrick Wolf on the APEX product development team, who helped me solve this problem in one minute.  Granted…Patrick was both the creator of the problem and the creator of the solution!

To resolve this problem:

  1. Open the page in Page Designer in Application Builder
  2. Edit the page attributes
  3. In the Advanced section of the page properties on the right hand side of Page Designer, change “Reload on Submit” to “Always” (changing it from “Only for Success” to “Always”)

That’s it!

Setting “Reload on Submit” to “Always” will POST the page and render the result using the behavior as it was in APEX 5.0 and earlier.  In APEX 5.1, if Reload on Submit is set “Only for Success” (the default), it will use the new optimized page submission process, and expect a specifically formatted JSON result returned from the APEX engine.  Obviously, when I employ a page process which overrides the HTP buffer and emit binary content (instead of a well-formed JSON result), the libraries on the page don’t know how to deal with that, and thus, results in this obtuse “Unexpected token r…” message.

How to increase inline dialog size in Oracle APEX Universal Theme

There are three predefined inline dialog sizes in UT.

Sometimes developer needs inline dialog with custom sizes, for example, large than largest of theme predefined. If you use Universal Theme,  the simplest way how to increase inline dialog is to add some css to inline dialog region definition:

In this example 800 is required dialog width in pixels, 540 – height respectively.

Oracle apex Classic report CSV download to Restrict the columns

Use the below code to restrict what are the columns download the csv download in Oracle apex Classic report.

1. if one column is hidden column, if you download the csv you want the hidden column
NVL(:REQUEST, 'MY_REQ') LIKE 'FLOW_EXCEL_OUTPUT%'
NVL(:REQUEST, 'MY_REQ') NOT LIKE 'FLOW_EXCEL_OUTPUT%'

 

Use the below code for CSV , XLS and other formats

  1. Hidden column code like below, this column data only download the below mentioned format
  2. NVL(:REQUEST, ‘MY_REQ’) IN(‘CSV’,’XLS’,’PDF’,’XML’,’RTF’,’HTMLD’)
  3. Active column Server side condition code (Copy the below code and paste PL/SQL Expression Column ), while download below mentioned formats, current column not downloaded in the report
  4. NVL(:REQUEST, ‘MY_REQ’) NOT IN(‘CSV’,’XLS’,’PDF’,’XML’,’RTF’,’HTMLD’)

Check the below example

https://apex.oracle.com/pls/apex/f?p=44666:1:16075116997358::NO:RP,1:P1_TIMELINE_ID:74