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’);

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

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.

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

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