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

window.open (URL, windowName[, windowFeatures]);

windowName
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 window.open 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

URL
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

windowFeatures
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

window.open("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.

f?p=1:2:3:4:5:6:7:8:9

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

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

SESSION
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

APEX_UTIL.CLEAR_APP_CACHE('100');

and the following code would clear the cache of page 100

APEX_UTIL.CLEAR_PAGE_CACHE('100');

URL Parameters

In most cases the URL is automatically handled by APEX and the developer does not need to understand it. However with more advanced use of APEX comes the need to manually specify certain aspects of the URL. The following is a quick explanation of how the URL is constructed.

For this explanation we will focus on the f?p= part of the URL and explain each specific parameter

f?p=1:2:3:4:5:6:7:8:9

1 - The number or alias of the application you wish to run. The alias can be set in shared components -> definition

To access the current application id, use &APP_ID.

2 - The page number or page alias. The page alias can be set in the page attributes

3 - The session. to access the current session, use &SESSION.

4 - The request term that you want to pass to the destination page

5 - DEBUG. Decide whether the page is run in debug mode or not. either YES or NO

6 - This parameter can accept two values. RP = resets the pagination of the destination page followed by a comma separated list of pages where you wish to reset the cache. So for example if you want to reset pagination and reset the cache for pages 1 and 2 you would include "RP,1,2" for this parameter

7 - Comma separated list of page items to assign values to.

8 - Comma separated list of values to assign to the page items specified at position 7. The values must be in the same order as the page items are

9 - Printerfriendly. Determines if the page is being rendered in printer friendly mode. YES or blank

Basics of Ajax

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 on it

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

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

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

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 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
what value the user had entered
*/
get.add('P1_EMPID',emp_id);


/*
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.

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.

http://forums.oracle.com/forums/message.jspa?messageID=2437324

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
}
else
return true; //filename is valid. so continue
}
}
</script>

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

How to stop the user from clicking a page submit button more than once

The standard way to push the page data back to the APEX server is to use an HTML button. One issue with HTML buttons is that they can be clicked more than once, and under certain circumstances this can cause issues. To ensure that the button can only be pressed once you can do the following.

alter button settings so that inside the URL redirect section the URL target value is set to

javascript:this.disabled=true;doSubmit('SUBMIT')

Note that the value inside doSubmit('') is the same as the button name.

You can even go one step further and replace the text of the button to something relevant like "Submitting....". The following code does this

javascript:this.disabled=true;this.value='Submitting....';doSubmit('SUBMIT');

The button now looks like this when it has been clicked

Thats it, the button is now coded so that the user can only click it once to submit the page. Any further clicks are ignored.

Integrating jQuery into APEX

jQuery is an open source Javascript library that enables the developer to produce concise, cross browser Javscript quickly and easily.

To integrate jQuery into Application Express you must first download the library from jquery.com. For performance reasons i would recommend downloading the production version.

Once you have downloaded the file you have to upload the file into APEX. Do this using create image within shared components

Now that the jQuery library is stored within APEX you can access it using the following code

<script type="text/javascript" src="#WORKSPACE_IMAGES#jquery-1.3.1.min.js"></script>

(you can upload the file to a specific application. if you do so then you must use #APP_IMAGES# instead of #WORKSPACE_IMAGES#)

This code can either placed inside the specific page where it is needed, or accessible to every page by placing it on page zero. If you wish to put it inside a page the sensible place to put it would be in the html header

Or, personally I prefer to place it inside the page template. This can be found within shared components.

For specific uses of jQuery please refer to docs.jquery.com/Tutorials

jQuery datepicker

Oracle's native datepicker is extremely primative and is in urgent need of an update. I do not like the fact that it pops up a new window, which in my experience can easily be lost behind other windows and forgotten about. Luckily there is a better solution out there, in the form of a jQuery datepicker. This does not require a new window and also does not require much to integrate into an APEX application. Here is a short guide on how to integrate jQuery datepicker into APEX.

The first step is to ensure that you have the jQuery library included in your application. Follow guidelines here to do this. Basically you upload the jQuery Javascript file to application images and include it in your app using the following code

<script type="text/javascript" src="#WORKSPACE_IMAGES#jquery-1.3.1.min.js"></script>

In addition to the jQuery library you must also download the jQuery ui package. this can be found at ui.jquery.com/download. jQuery allow you to create a custom download. This has the benefit of reducing the size of the file while still giving you the features that you want to use. When creating the package ensure that you select the ui core and the datepicker widget


Once the file has been downloaded, upload the script to the application images and include the script in your application. This is done in the same as the core jQuery file.

As well as including the javascript file you must also include the following CSS files. These are stored inside the theme folder

• ui.datepicker.css
• ui.theme.css

Once these files are uploaded to the shared components > images, use the following code to include the css in your application

<link rel="stylesheet" href="#WORKSPACE_IMAGES#ui.datepicker.css" type="text/css">

<link rel="stylesheet" href="#WORKSPACE_IMAGES#ui.theme.css" type="text/css">

So far we have the following code in place to include the relevant Javascript and CSS files to allow you to integrate the datepicker

Before creating the datepicker field in the app we must define how jQuery will attach the datepicker functionality to items on our screen. This can be done on an item by item basis but for simplicity i will assign the functionality to all items with the class of "datepicker"

<script>

$(document).ready(function(){

$(".datepicker").datepicker({
showOn: "both",
buttonImage: "#IMAGE_PREFIX#asfdcldr.gif",
buttonImageOnly: true,
dateFormat: 'dd/mm/yy'
});

});

</script>

Finally create an item with a type of "text" and assign it a class of datepicker

now when you run the page you should have a date field with a datepicker popup that is infinitly nicer than the in built Oracle datepicker

view example here

jQuery Thickbox

A jQuery thickbox allows you to create a popup region without actually opening a new browser window or tab. The thickbox region can contain some content already on the page, usually hidden when the page is viewed normally, or the content of another page.

Before starting you must have included the jQuery library in your APEX application. For instructions on how to do this check the guide Integrating jQuery into APEX

Once you have jQuery installed successfully you must also download the specific files for the thickbox, these can be retrieved from jquery.com/demo/thickbox/. Download the javascript, css files.

Include the javascript and css files in your application using the following code

<script type="text/javascript" src="#WORKSPACE_IMAGES#thickbox.js"></script>
<link rel="stylesheet" href="#WORKSPACE_IMAGES#thickbox.css" type="text/css">

To use the thickbox to display an existing region do the following

Create a region with "No template" and place the following in the region header

<div id="thickbox_region" style="display:none;">

And close the div in the footer

</div>

Now create some content in the region to give the thickbox some content. For example I will create two items, P2_TEXT and P2_DATE. These items are purely dummy content.

Next, create a region to hold the buttons that will launch the thickbox region. This region does not require any specific settings.

Finally create the button that will launch this region. First create a button with the default regions, then once the button has been created alter the following settings.

For the optional URL redirect enter

javascript:return false;

And for the button attributes enter

class="thickbox" alt="#TB_inline?height=300&width=400&inlineId=thickbox_region"

You will notice from the alt tag that you can alter the width and height settings for the tickbox. For details of other settings visit jquery.com/demo/thickbox/

Now when you run the page and click the button the thickbox region should appear like the following example.

To view the above example click here

Manually set page success/failure message

APEX allows you to define a success or failure message when running a page process. It is also possible to do this manually using the following code

apex_application.g_print_success_message := 'Message you want to display';

for the message to show, ensure that you tick the checkbox on the relevant branch to "include process success message"

Oracle APEX Interactive report based on PLSQL function

With the latest release of Application Express, Oracle have included a brand new reporting widget called Interactive Reports. They have managed to make it so simple to have fully functioning report where the only effort from the developer is creating the SQL query. Unfortunately Oracle have failed to allow the report to be based on a PLSQL function returning the query. This omission is a serious oversight by Oracle, as all my reports use this technique. Until Oracle update APEX to include this feature here is a workaround to display an interactive report based on a query stored in the database.

Step1.
Create a collection based on a query. The code for this should be placed in a before header process

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => ‘IR_TEST’,
p_query => function_returning_query );

Step 2.
Create an interactive report querying a collection.

Select *
From apex_collections
Where collection_name = ‘IR_TEST’;

Step 3.
You will notice that if you run the report that the column headers do not correspond to your dynamic query. This is because the IR is querying the collection and therefore using the column names from that. To resolve this, create hidden page items(eg P1_COL_HEAD1, P1_COL_HEAD2 etc) to hold the correct column name. Ensure that you create enough to hold the maximum expected columns. (I create 50 since this is the maximum a collection can hold). For each of these items replace the cxxx column header text in the report attributes for the relevant page item. eg. C001 for &P1_COL_HEAD1. and C002 for &P1_COL_HEAD2. (remember to include the dot).

Step 4.
Create a process that will populate the heading page items with the relevant column name values from the dynamic query. This can be done by splitting the select line using the commas and extracting the dynamic column headers.

Step 5.
Now that the column headers are displaying correctly we should hide any irrelevant columns. Because a collection has 50 main columns there can be many columns without any data, and because this report is meant to be dynamic we want the columns to dynamically display whenever there is any data in them. Go into the report attributes and go into every column starting cxxx and set a conditional display type of exists and enter the following SQL. Replacing c001 with the relevant column header.

Select 1
From apex_collections
Where c001 is not null;

Finally set the conditional display to never for the following columns collection_name, seq, clob001, md5_original.

And that’s it! The method outlined above does seem a bit extreme to allow an interactive report to display a dynamic query but currently it is the only workaround I know of.

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 http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html , 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.