Creating A Popup Window

Standard behaviour in APEX is for a branch to link to a page using the current window (or tab). However there are times when it is desirable for the application to link to another page in a popup window. This may be popup help, like the standard help in APEX, or maybe a print preview page. The following is how to create a popup window.

To create the popup window I am going to use Javascript. This can be placed behind a button or text link, or it can be placed in an HTML region.

The code to create the popup is very simple (URL, windowName[, windowFeatures]);

The name of the popup window. This name is not visible by the user, and is only there for reference by Javascript. For the example below I have decided to use the name popup1. If was called again, using “popup1” as the windowName, then the contents of the popup would be refreshed with this new call. For this reason it is a good idea to use unique names for each window

In the example code below I will simply call page 200 without passing in any page item values. If you wish to pass in values, or pass in a request value then please check out the post on URL parameters

This allows the developer to define specific features for the popup window. For example, no scrollbars, the size of the window and whether certain menu bars are visible. For a full list of features visit here

The Javascript“f?p=&APP_ID.:200:&SESSION.”,”popup1″);

The above line of code will open a new window (or tab depending on browser settings) called popup1, containing page 200. Easy.

A couple of things to note when using popups.

You will see that I have passed in the current session value to the new popup. This is useful as it allows the window to have access to all your current session values, as well as knowing that you have passed authentication. One issue with using the same session is that the two windows could be able to access(or more importantly, alter) the same session values at the same time. This can cause unexpected results.

Therefore it is sensible to have the popup as a deadend in your application. For example this could be a single page containing a print preview of the current page, with no links to any other page. For this reason it is a good idea to use a template specific to popup pages. This template should be as blank as possible and have little or no navigation items in it.

Another issue is the use of popup blockers. In most cases it is not known whether the end user is using a blocker, and how that blocker will behave when the popup is called. Unfortunately there is no easy answer to this and must be addressed on a case by case basis.

Clearing Cache

In another page I explained the format of the URL.


Parameter number 6 allowed you to reset the cache for specific pages. Sometimes however, there is a need to reset the cache for the whole application. This can also be done using this parameter with the following two options

Clears cache for all pages and all application-level items in the current application and removes sort preferences for the current user.

Achieves the same result as the APP keyword, but clears items associated with all applications that have been used in the current session.

There is also a way to reset the cache using PL/SQL. The following code would clear the cache of application 100 for the current session


and the following code would clear the cache of page 100


APEX and web 2.0

For a while now the internet buzz word has been “web 2.0”. Many people new to APEX are keen to see how it can handle these new features. Luckily Oracle has created a page dedicated to this subject. Key points from the site are listed below, to view the full page visit

PPR Reporting and Charting
Partial Page pagination of reports is provided by templates included in each theme. These can be easily extended to customer specific themes. To test partial page refresh, simply create any report page that returns enough rows to be paginated. Then, paginate the report and notice that only the report changes, the other components of the page are not refreshed.

Auto Updating Reports and Charts
Click to see an example of Auto Updating Reports.

AJAX Region Pull
You can dynamically generate a report based on a select list.
Click to see an example of AJAX Report Filters.

Reports with AJAX based Detail Frames
This example shows how you can dynamically show report row detail inline with a report.
Click to see an example of Reports with AJAX based Detail Frames.

AJAX-Based Cascading Select Lists
This example shows how one select list on a page can drive the values of another select list on the same page, without doing a full page refresh. Click to see an example of AJAX-Based Cascading Select Lists.

Integrate your favorite AJAX/DHTML library
With Application Express, you have the ability to use any server agnostic AJAX/DHTML library. For example, dojo, jquery, prototype,

On Demand Processes
Oracle Application Express supports an On Demand Process component that can be invoked using AJAX. This is a built in framework for client-side communication (which respects security) between a rendered page and Application Process On Demand or Application Pages. It has the ability to access current session state or set session state of items. This is what implements most every example you see on this page.

Dynamic Menus
The Document Library packaged application is a great illustration of providing dynamic pull down menus. Each pull down menu uses AJAX controls to determine a user’s domain of actions.

File Browse – filename length limit

I use the built in “File Browse” item in many of my applications and for a long time I had not experienced any issues. However, recently I noticed that there was an issue when using large filenames. If I selected a large filename and submitted the page a HTML error page was displayed. What surprised me was that this was not the usual APEX error page. After some investigation I found an interesting post on the APEX forums at Oracle.

It seems that there is a limit of 70 characters for the filename. It should be noted that this limit is solely for the filename, and does not include the path. This limit is purely down to how APEX internally stores the file in wwv_flow_files.

I have implemented the validation check described in the thread, with very slight changes to better suit my needs. The following is how I have done this.

Firstly I created the Javascript

<script type=”text/javascript”>
function checkFileName(pThis){
if (pThis.value != “”) {
//get path value (including file name)
var fileAndPath = pThis.value;
//find the index of the last “”
var lastPathDelimiter = fileAndPath.lastIndexOf(“\”);
//get everything after the last “”
var fileNameOnly = fileAndPath.substring(lastPathDelimiter+1);

if (fileNameOnly.length >70) {
alert(“File name ” + fileNameOnly +
” is too long. Filename can be maximum of 70 characters in length”);
return false; //filename is not valid
return true; //filename is valid. so continue

In addition to displaying an error message if the filename is greater than 70, I decided to return a Boolean value to indicate whether the filename had passed the test or not.

This boolean could then be used along with a button to submit the page when the filename passed the test, or remain on the page if the name fails.

I created a button called UPLOAD with the following code within the URL Redirect.(F1_FILE_NAME is the name of the file browse item)

javascript:if(!checkFileName($x(‘P1_FILE_NAME’)))return false;doSubmit(‘UPLOAD’);

The above example is very simple but I think that implementing something similar is essential if the built in “File Browse” button is used in a production application.

Thank you goes to Mike and Arie for their examples in the aforementioned thread

Recover saved interactive reports after an application import

During a recent update of my live APEX system I noticed that the user’s saved interactive reports had been lost. I had made numerous updates of the live system before and never experienced this issue before. I found an interesting post from David Peak detailing how to avoid the loss of the saved reports , but before doing this I had to understand exactly what had happened and hopefully work out how to recover the lost reports.

My normal workflow would be to develop in my development environment and then progress this application through testing and into live, preserving the application id through every progression. In this specific instance I had taken a cut of the application from live and imported it into development using a temporary id. On each subsequent promotion I then changed the id to the original, and correct, id. It seems that this changing of the id causes the issue.

Now that I understood how the issue occurred and, thanks to David Peake, understood how to stop this from happening again, I wanted to recover the reports and hopefully calm down irate users.

The saved reports are saved in the table flows_030100.wwv_flow_worksheet_rpts.



Key columns are as follows
flow_id – the id of the application
page_id – the page number where the interactive report is situated
Worksheet_id – the internal unique id
session_id – being blank indicates that this is the “master” version of the individual users saved report.

So now I know where the data is stored, the next step is understanding the data. From David Peake’s site mentioned earlier, I know that the worksheet id is the unique identifier for the interactive report, but I knew little else.

After ordering the data based on the date found in the updated_on column I noticed that there was a distinct change in the workspace ID, around the same time that I had done the upgrade. From there it was straightforward enough to realise that I had to update the previous records to include the new worksheet_id that APEX had automatically assigned to the Interactive Region. I also noticed for each unique user and interactive report there were many rows. Although I do not fully understand the use for these multiple rows, except for perhaps auditing purposes, it is clear(after some tests) that the actual saved report is defined by the row with the blank session_id.

Now that I had a bit of understanding of the data I could attempt the recovery. The following is a brief overview of how I actually did it.

  1. Determine the new worksheet_id for the specific interactive report where the saved reports are missing. For this, focus on the flow_id(application_id) and page_id. Ensuring that you select data dated after the application upgrade. If there are no rows present for the specific interactive report, then a quick visit to the relevant page in the application will create a row. Simply visiting the report will create a row, but saving a report will allow it to be found even easier. Do not worry about saving a report in a live system, as this report is linked solely to the user you logged in as.
  2. Now that you have the correct worksheet_id, you have to determine the rows that contain the data for the saved reports. To do this follow the above procedure, but only include those rows which fall within the date range of your last version of the application.
  3. Now simply update the rows containing the saved reports with the new worksheet_id.

A couple of points about the above

  1. Before modifying the data you have to unlock the flows_030100 user (or the flows user for whatever version of APEX you use).
  2. There is a unique key present that will stop you from having multiple saved reports with the same name, for the same report and for the same user. To get round this you can alter the name to something like, “report A (old)”. I’m sure you will think of something better than this, but you get my drift.
  3. Remember that you are messing with core APEX tables, and messing with these tables can cause issues with your APEX installation as a whole. Basically, make a backup before you start.

Hopefully the above is of some use to you if you do experience the same issues as I did.

Using AJAX in APEX

In Application Express(APEX) it is possible to communicate with the database without submitting the entire page. This is extremely useful when your APEX application is being run on a very slow network. This on demand communication is possible in APEX using Asynchronous JavaScript and XML(Ajax). Using this method sends only the minimum information required to retrieve a value from the database. I will explain using a simple example involving the famous emp table.

lets say I have a page that has three items and one button

  • P1_EMPID. this will be a textfield used to enter the emp_id
  • P1_ENAME. this will be used to display the ename
  • P1_DISPLAY with the relevant ename for the given emp_id
  • SUBMIT. this will be a button that will submit the page. this page submission will fire off a process that will populate

The issue with the above example is that the entire page has to be reloaded when in fact only one small section will be updated, namely P1_DISPLAY. the following example will demonstrate how to retrieve the name from the database without refreshing the page.

1. Move the database code from the page process and place it in an application process. Name the process “GET_ENAME” and give it a type of “On Demand”. example code is below

v_ename emp.ename%type;
select ename
into v_ename
from emp
where emp_id = :P1_EMPID;
exception when others then htp.prn(‘An error occurred retrieving the name’);

2. Create the following javascript function.

function get_ename(){

retrieve the emp_id from the page
var emp_id = document.getElementById(‘P1_EMPID’).value;

define the Ajax call. The only variable of note in this example is the</span><span style=”font-weight: bold;”> application_process, which I have set to be the same name as step 1.
var get = new htmldb_Get(null,html_GetElement(‘pFlowId’).value, ‘APPLICATION_PROCESS=GET_ENAME’,0);

add the value in P1_EMPID into the session value for P1_EMPID. this is important as without this step the APEX server would not know
<span style=”font-weight: bold;”>what value the user had entered

call the ondemand process and accept the returning ename
var gReturn = get.get();

set the field on the page to equal the ename retrieved from the database
document.getElementById(‘P1_ENAME’).value = gReturn;


3. Finally alter the SUBMIT button so that it uses a URL redirect and in the URL field enter

javascript:get_ename();return false;

and thats it, when you press the submit button it will now send the emp_id to the database and populate P1_ENAME with the relevant name. and all without refreshing the page.

obviously this is a trivial example but it gives an idea of the possibilities of Ajax.