Duncan Mein's Blog

Most client sites I have been on tend to love wizards that hold the users hand through often complex business process / workflows. As a general rule, if I have a 10 step wizard (I generally use 1 page per step) I like to ensure that all 10 pages have their cache cleared as the user enters the wizard to ensure no values from a previous attempt to complete the process are hanging around. This is typically done by enter a comma delimited string of all the page numbers you wish to clear the cache of upon branching from page to page. In an ideal world, what I wanted to do was take advantage of the fact that I had taken to time to put all 10 pages into a page group and clear the cache of ALL pages contained within. Furthermore, using this logic, pages can come and go from the page group without the need to worry about how this effect my clearing of session state. Pretty cool huh, but wait, "APEX only allows you to enter page numbers and not page group names into the clear cache text...

Now as anyone who knows me me will confirm, I have always been a long admirer of VMWare and their suite of excellent virtualisation products. I started with Workstation before switching to Fusion on OS X and finally made the jump to the way cool ESXi a few years back (I blogged about this here ). Recently however, I am finding less and less time to install guest linux OS, configuring it for use with an Oracle Database (or Application Server), making sure all the RPM's, kernel values are correct etc. Yes you can use the Oracle Validated Packages to help simplify this (see Tims comments below for how to obtain these) but what I really value these days is the simplest configuration for home / home office use that is pretty much turn on and play. After a quick trawl of the Virtual Appliances section of the VM Market Place, I was left wanting until I remembered that Oracle were now dabbling in the Virtual Machine space. After a quick dl of VirtualBox (their virtualisation offering)...

I have just read the latest Statement of Direction from Oracle ( download here ) and there are some comforting statements from Oracle about there long term commitment to Application Express. For those like me who have been using APEX since the early days, this is good news as it represents a very clear intent that Oracle are very serious about APEX as key development framework. Nice read! Cheers Oracle.

We are looking for an APEX developer for an initial 3 month contract with definite scope for long term extension for a role in Hampshire (UK). Candidates must be SC cleared or willing to undergo clearance to work on a UK MoD site. Any interested parties, please send me an up to date copy of your CV with availability and rate to: duncanmein@gmail.com

A fairly recent requirement meant that we had to send a bulk email to all users of each department from within our APEX application. We have 5000 records in our users table and the last thing we wanted to do was send 5000 distinct emails (one email per user) for both performance and to be kind on the mail queue / server. In essence, I wanted to to perform a type of string aggregation where I could group by department and produce a comma delimited sting of all email address of users within that department. With a firm understanding of the requirement, so began the hunt for a solution. Depending on what version of the database you are running, the desired result can be achieved in a couple of ways. Firstly, the example objects. CREATE TABLE app_user (id NUMBER ,dept VARCHAR2 (255) ,username VARCHAR2(255) ,email VARCHAR2(255) ); INSERT INTO app_user (id, dept, username, email) VALUES (1,'IT','FRED','fred@mycompany.com'); INSERT INTO app_user (id, dept, username, em...

If you have ever used a self referencing table within Oracle to store hierarchical data (e.g. an organisations structure), you will have undoubtedly used CONNECT BY PRIOR to build your results tree. This is something we use on pretty much every project as the organisation is very hierarchy based. Recently, the support cell sent the details of a recent call they received asking me to take a look. Looking down the call, I noticed that the following Oracle Error Message was logged: "ORA-01436: CONNECT BY loop in user data" A quick look at the explanation of -01436 and it was clear that there was a circular reference in the organisation table i.e. ORG_UNIT1 was the PARENT of ORG_UNIT2 and ORG_UNIT2 was the PARENT of ORG_UNIT1 . In this example, both ORG_UNITS were the child and parent of each other. Clearly this was an issue which was quickly resolved by the addition of a application and server side validation to prevent this from re-occurring. The outcome of this fix was ...

Just a quick plug for a cool Penetration Test tool that we have been using on-site for a few months now. The application is called: Application Express Security Console and developed by a company called Recx Ltd This can be used to identify areas of you APEX applications that are vulnerable to: SQL Injection, XSS as well as inadequate access control etc. It kindly suggests ways in which the vulnerability can be addressed as well. We have built the use of this into our formal release process now and has definitely proved value for money to organisation.

Recently our test department raised a bug against one our applications that occurred when trying to insert a record into a table. The error message encountered was a fairly innocuous " ORA-01704: string literal too long ". Following the test case to the letter, I successfully generated the same error and located the table that the APEX form was inserting into. A quick check of the Data Dictionary confirmed that the column in question was of type VARCHAR2(10). At this stage, I though the obvious cause was that there was no limit on the APEX form item (a Text Area) of 10 characters. Having checked the item in question, not only was there a “ maxWidth ” value of 10, the text area had been created with a “ Character Counter ”. Strange then how a form item accepting 10 characters was erroring whilst inserting into a column of VARCHAR2(10). A little while later...... (after some head scratching and several discussions with our DBA’s and a colleague) the problem was all too clear. S...

The following query is a very simple way of identifying all report columns within your APEX application that may be exposed by Cross Site Scripting (XSS). XSS allows an attacker to inject web script (JavaScript) into an application and when this is rendered in the report, the script is interpreted rather than rendered as text. To safe guard against this attack, APEX provides a "Display as Text (escape special characters)" report column attribute that can be applied to classic and Interactive Reports. This causes the script text to be displayed as text rather than interpreted by the browser. If you have any markup (HTML) within your query that the report is based on, this markup will also be displayed as text and not interpreted. I personally think this is a good by product as you should not really be coding look and feel into your raw SQL. Anyway I digress. Here is the query that will identify all vulnerable report columns within your APEX application: SELECT application_id...

I have been using Oracle SQL Developer Data Modeller for a while now within a Windows XP environment. It seems pretty good (albeit a little slow but hey show some an Oracle Java client application that is quick. Oracle Directory Manager?, OWB Design Centre? I shall labour this point no more) and I was looking forward to trying it out on my new 27" iMac. I promptley downloaded the software from OTN and a quick read of the instructions suggested I need to do no more other than run the datamodeler.sh shell script since I already had Java SE 6 installed. As it turns out, the datamodeler.sh script in the root location does little more than call another script called datamodeler.sh found in the /datamodeler/bin directory which is the once you actually need to execute to fire up SQL Data Modeler When this script runs, it prompts you for a the full J2SE file path (which I had no idea where it was) before it will run. After a quick look around google and I came across the command: java_ho...

This cool example is not one I can take the credit for but since it is used pretty heavily in our organisation, I thought I would share it as it's not only pretty cool buy also demonstrates how useful Oracle Pipelined functions can be. In essence a Pipeline table function (introduced in 9i) allow you use a PL/SQL function as the source of a query rather than a physical table. This is really useful in our case to generate all the days in a calendar month via PL/SQL and query them back within our application. To see this in operation, simply create the following objects: CREATE OR REPLACE TYPE TABLE_OF_DATES IS TABLE OF DATE; CREATE OR REPLACE FUNCTION GET_DAYS_IN_MONTH ( pv_start_date_i IN DATE ) RETURN TABLE_OF_DATES PIPELINED IS lv_working_date DATE; lv_days_in_month NUMBER; lv_cnt NUMBER; BEGIN lv_working_date := TO_DATE(TO_CHAR(pv_start_date_i, 'RRRRMM') || '01', 'RRRRMMDD'); lv_days_in_month := TRUNC(LAST_DAY(lv_working_date)) - TRUNC(lv_work...

Server virtualisation is something I have been using for a few years now both at work and at home. I mainly use VMWare Fusion on my MacBook Pro and VMWare Workstation / Player on an old Windows Laptop. For everyday tasks these products are amazing as i can run an XP VM on my MacBook Pro for all those times I need to open MS Project or run TOAD. On my server at home, I didn't want to install a host OS and then install VMWare Workstation / Server to host several Linux VM's. Instead I explored the option of a "Bare Metal" hypervisor from VMWare (ESXi 3.5 Update 3). A hypervisor is a very small linux kernel that runs natively against your servers hardware without the burden of having to install a host OS. From here you can create and manage all your VM's remotely using the VMWare Client tool. For more information in ESXi, check out VMWare's website here Reading the documentation suggested that ESXi was very particular about the hardware it supports and so began th...

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy. For example: CREATE TABLE DEPT ( DEPT_ID NUMBER NOT NULL ,PARENT_ID NUMBER ,DEPT_NAME VARCHAR2 (100) NOT NULL ,EMPLOYEES NUMBER NOT NULL ,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID) ,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID) REFERENCES DEPT (DEPT_ID) ); INSERT INTO DEPT VALUES (1,NULL,'IT', 100); INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12); INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15); INSERT INTO DEPT VALUES (4,1,'TEST', 25); INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2); INSERT INTO DEPT VALUES (6,2,'Java', 1); INSERT INTO DEPT VALUES (7,2,'SQL', 11); INSERT INTO DEPT VALUES (8,2,'C++', 3); INSERT INTO DEPT VALUES (9,4,'Functional', 3); INSERT INTO DEPT VALUES (10,4,'Non Functional', 5); COMMIT; A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child rel...

There is a really cool JavaScript function in Apex called: $f_CheckFirstColumn that allows you to Check / Uncheck all checkboxes that exist in the 1st column position of a Tabular Form / Report. To implement this, all you need do is add the following HTML to the Column Heading of the 1st Column in your Tabular Form (i.e. the Checkbox Column): <input type="Checkbox" onclick="$f_CheckFirstColumn(this)"> Check out an example here

Some of my colleagues are working on a project where bandwidth is massively limited (64k). One suggestion to increase application response time was to use MOD_GZIP (an open source compressor extension to Apache) to compress the outbound HTTP traffic. The only drawback is that MOD_GZIP is not supported by Oracle. Since we are using Oracle Application Server, Web Cache achieves exactly the same by simply adding a compression rule to Web Cache for the URL Regular Expression /pls/apex/.*$ We noticed that without any compression of the HTTP outbound traffic, our test page took 30 seconds to fully render on a 64k link. Turning on compression reduced the rendering time to 7 seconds. Very impressive. Navigating through an application with compression turned on was noticeably quicker than one without compression. To test if your outbound HTTP traffic is compressed, I would grab the Live HTTP Headers extension to Firefox and you are looking for a line like: Content-Encoding: gzip in the outbound...

I noticed in one of the comments of Dimitri Gielis articles that Jornica pointed out a script called apxXEpwd.sql I ran this as the SYS user and sure enough, it allows you to reset the ADMIN password for the internal workspace. This script can be found in the root apex directory that you download from OTN. Very useful when you forget what that password is!

I will be honest and admit that I have not been SQL Developers biggest fan since it's release a few years ago. Having always used 3rd party products such as TOAD and PL/SQL Developer, I found certain things a little irritating and not overwhelmingly obvious when forced to use SQL Developer. Recently however, I found an absolute god send of a feature in SQL Developer and that was the ability to import / export APEX applications. This means that the Web GUI for such actions is no longer required. Very simply, you connect to your parsing schema's database account via SQL Developer, right click on the Application Express tree directory and select Import Application. This opens a simple wizard and off you go. One other cool feature of this is that you can open the details window and see exactly what your import is doing, something that is not possible when using the web GUI. Download SQL Developer from: OTN

Just upgraded from APEX 3.1 to 3.1.1 on an Oracle Enterpise Linux 4 Update 4 platform. Intall took: 5:39 and termintaed without error. The patch can be downloaded from metalink (patch number 7032837) All in all, a very simple upgrade and now onto the task of regression testing our current 3.1 apps

One of the really nice features of APEX is the ability to hide / show regions when editing item or page attributes. This is really useful if you have to update the same attribute for multiple items. If you want to implement this type of approach on a page you simply need to: Create a New Region Template Either create a new region template (or copy an existing one) and add a DIV tag with the id="XXX#REGION_STATIC_ID#" around the Definition Template. Don’t forget to close the DIV at the end of the template. In this example I copied our region called “Reports Region” and named it “Reports Region 680 Width (Static ID)” Add the following JavaScript to your Page Header: view Create several regions on your page and assign a unique Static ID i.e. REGION1, REGION2 etc. Set the Region Template to the one you created in the 1. Create a button for each Region you defined in the previous stage and add the following JavaScript call as the URL target of each button: javascript:hideDiv(‘XXXR...

To set the TAB order of your form, you simply need to add the attribute: TABINDEX="1" to the "HTML Form Element Attributes" of your items replacing the number with the desired sequence. This is a great way of enhancing the usability of your forms.