Posts

Oracle VirtualBox... a good thing.

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)

Oracle, APEX and Longevity!

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're Hiring

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

Native String Aggregation in 11gR2

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

A Right Pig's Ear of a Circular Reference

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

Pen Test Tool for APEX

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.

Beware of the Byte

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