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,
  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,
  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,
  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:

    l_file_blob blob; 
    l_file_name apex_application_files.filename%type; 
    l_file_mimetype apex_application_files.mime_type%type; 
    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.wpg_docload.download_file( l_file_blob ); 
    -- Stop page processing 
    apex_application.stop_apex_engine ; 

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.

Delete a Row of a Report with a Dynamic Action

I was asked recently how I implemented the deleting of a row in my demo application, so I thought I would post the answer here in case anyone else was curious.

To accomplish the deletion of a row with a prompt to the user for confirmation you will need a hidden page item and a dynamic action.

On the page that has your report, add a hidden page item – mine is called P20_DELETE_ID. This item will be used by our dynamic action to hold the primary key of the row on which the user clicked the delete icon.

You need to add a link column to your report for the delete icon with the following attributes:

Link Target: URL
URL: javascript:void(null);
Link Text: <span class="t-Icon fa fa-trash delete-note" aria-hidden="true"></span>
Link Attributes: data-id=#REMOVE#
Link Column

Notice in the Link Text that I added a class called delete-note. This is the jQuery selector we will use to trigger our dynamic action. Also, notice the Link Attributes – the column referenced here, in my case #REMOVE#, should hold the primary key for the row. This value will be used to identify the row that needs to be deleted in your delete PL/SQL statement.

Next, we need a dynamic action that fires on click of our jQuery selector, .delete-note. This action will have four true actions:
Delete Row Dynamic Action

True Action #1: Confirm
Text: Are you sure?

True Action #2: Set Value
Set Type: JavaScript Expression
JavaScript Expression: $(this.triggeringElement).parent().data('id')
Affected elements:
Selection Type: Item(s)
Items(s): P20_DELETE_ID <—- your hidden page item
Set Value

True Action #3: Execute PL/SQL Code
PL/SQL Code: delete from jsd_notes where id = :P20_DELETE_ID;
Items to Submit: P20_DELETE_ID <—- your hidden page item
Execute PL/SQL Code

True Action #4: Refresh
Selection Type: Region
Region: Notifications <—- your report region

And that should do it 🙂
– Jackie –

Soruce from

How to show and hide inline dialog in oracle apex using JavaScript

How to show and hide inline dialog in oracle apex using JavaScript

  1. Create a Region named as example “Employee Details:
  2. Assign a Static ID of the region “EMPDTLS”  ( ..Region Property/Advanced/Static ID:)
  3. Create a Page Button Named “ShowDialog”
  4. Go to the button property and set it’s properties as below:
  5. Behavior/Action = Redirect to URL
  6. Target = javascript:openModal('EMPDTLS');
  7. Now Save and run the page. when you click “ShowModal” button the modal dialog will be appeared.
  8. Now for closing the Modal dialog, you can add a new button “closeDialog” in the dialog region and set the properties as below:
  9. Behavior/Action = Redirect to URL
  10. Target = javascript:closeModal(‘EMPDTLS’);
  11. If you want to pass value in the Dialog region then




#EMPNO# – this is applicable when you calling dialog window from Interactive or classic report. here EMPNO is the column name.



Select All / Unselect All Checkbox in Interactive Report Header

Select All / Unselect All Checkbox in Interactive Report Header


I want a checkbox in an Interactive Report (IR), and I want the users to be able to quickly Select All or Unselect All of them (but only for rows that were rendered on the page). I don’t want two big clunky buttons to do this, I just want a single checkbox in the header like I see on cool peoples’ web sites.

To do this:

1. In the Region Definition for the IR, add the checkbox to the query, e.g.:

       apex_item.checkbox(1, record_id) selected
FROM   ...

Also, set the region Static ID to some value, e.g. myreport This will be referred to by the dynamic action.

2. In the Report Attributes for the IR, modify the attributes of column “SELECTED”:

Heading =

<input type="checkbox" id="selectunselectall">

Escape Special Characters = No

Enable Users To = (uncheck all options, including Hide, Sort, etc.)

3. In the page definition, add a Dynamic Action:

Event = Change
Selection Type = jQuery Selector
jQuery Selector = #selectunselectall
Event Scope = Dynamic
Static Container (jQuery Selector) = #myreport

True Action = Execute JavaScript Code
Fire On Page Load = (uncheck)
Code =

if ($('#myreport #selectunselectall' ).is(':checked') ) {
  $('#myreport input[type=checkbox][name=f01]').prop('checked',true);
} else {
  $('#myreport input[type=checkbox][name=f01]').prop('checked',false);

The only issue with this is if the user clicks “Action” and “Select Columns”, the checkbox item shows the html code (”

UPDATE 18/5/2017: updated for multiple IRs on same page (APEX 5+)
UPDATE 23/5/2017: updated to recommended prop instead of attr


thanks to  : https://jeffkemponoracle.com/2012/11/select-all-unselect-all-checkbox-in-interactive-report-header/

Make input value uppercase in CSS without affecting the placeholder

input { 
    text-transform: uppercase;
::-webkit-input-placeholder { /* WebKit browsers */
    text-transform: none;
:-moz-placeholder { /* Mozilla Firefox 4 to 18 */
    text-transform: none;
::-moz-placeholder { /* Mozilla Firefox 19+ */
    text-transform: none;
:-ms-input-placeholder { /* Internet Explorer 10+ */
    text-transform: none;
::placeholder { /* Recent browsers */
    text-transform: none;

How To Convert Number into Words using Oracle SQL Query

How can you convert a number into words using Oracle Sql Query? What I mean by Number to Word is:

12 = Twelve
102 = One Hundred Two
1020 = One Thousand Twenty

Here’s a classy query which will convert number into words.Please see the query below:

select to_char(to_date(:number,'j'),'jsp') from dual;

If I pass 234 in number, then the output will : two hundred thirty-four

SELECT TO_CHAR (TO_DATE (234, 'j'), 'jsp') FROM DUAL;
//Output: two hundred thirty-four

SELECT TO_CHAR (TO_DATE (24834, 'j'), 'jsp') FROM DUAL;
//Output: twenty-four thousand eight hundred thirty-four

SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
//Output: two million four hundred forty-seven thousand eight hundred thirty-four

So how the query works? Well here’s why:

If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713 BC), basically this date is been used for astronomical studies.

So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.

If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.

Now to_char(to_date(:number,'j'),'jsp')jsp = Now; take that date(to_date(:number,'j')) and spell the julian number it represents

Limitation & workaround

There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:

ORA-01854: julian date must be between 1 and 5373484

To cater the above problem ,create a function,

and with little trick with j->jsp ,you can fetch the desired result.

create or replace FUNCTION spell_number (p_number IN NUMBER)
   TYPE myArray IS TABLE OF VARCHAR2 (255);

   l_str myArray
         := myArray ('',
                     ' thousand ',
                     ' million ',
                     ' billion ',
                     ' trillion ',
                     ' quadrillion ',
                     ' quintillion ',
                     ' sextillion ',
                     ' septillion ',
                     ' octillion ',
                     ' nonillion ',
                     ' decillion ',
                     ' undecillion ',
                     ' duodecillion ');

   l_num      VARCHAR2 (50) DEFAULT TRUNC (p_number);
   l_return   VARCHAR2 (4000);
   l_pisa     varchar2(50);
   FOR i IN 1 .. l_str.COUNT
      EXIT WHEN l_num IS NULL;

      IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
         l_return :=
            TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
            || l_str (i)
            || l_return;
      END IF;

      l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);

    l_pisa := substr(p_number,instr(p_number,'.')+1,length(p_number)-instr(p_number,'.'));
    if instr(p_number,'.') > 0 and l_pisa < 99  then
    l_return := upper(l_return||' and '|| to_char(to_date(l_pisa, 'J'), 'JSP') ||' Paise Only');
    end if;
   RETURN l_return;

SELECT spell_number (53734555555585.13) FROM DUAL;