Duncan Mein's Blog

The current application I am working on involves a re-write to a 12 screen wizard that was written 18 months ago. Several of the screens make use of manually built tabular forms (SQL report regions) and collections to hold the values entered. Some of the screens in the wizard have multiple tabular forms on them as well. Currently all tabular forms have 15 lines which cannot be added to or deleted from. In the new version, we removed this limit and allow the user to add as many rows as he / she needs. Furthermore, specific rows can now be removed from the Tabular form. Since all entered data is written into collections, we wanted to avoid " line by line " processing i.e. submitting the form for each time, updating the collection and branching back to the page. By utilising some simple JavaScript and the new " Static ID " of the Reports Region new to APEX 3.0, all requirements could be met. The Static ID attribute of the reports region allow us to add our own (unique)...

I bought a Mac Book Pro about 8 months ago as my main business Laptop. Coupled with a copy of Parallels, I built my Oracle Server (Database and Apps Server) on a Windows VM environment which left Mac OSX free for Development using SQL Developer, Dreamweaver etc A couple of weeks back I decided to upgrade to OSX Leopard and install windows natively using Bootcamp to utilise both core's on the CPU and all 3 Gig of memory. All well and good until i tried to use the Hask key (Alt + 3 in OSX) when working on some APEX templates. After much research on the web, it appears that most OSX key mappings are installed when using bootcamp but in order to print the hash (#) symbol, you must use Ctrl + Alt + 3 Simple when you know how

After the lastest round of updates were installed from Microsoft, I foolishly forgot to uncheck the "Upgrade to I.E 7" box and hence after a reboot, a fresh new version of Internet Explorer was waiting for me. On face value this appeared ok until I tried to access Mercury Test Director. According to the error message, only I.E 6 was supported. A quick search on google and I happened upon this website: http://tredosoft.com/Multiple_IE and downloaded the installer which contained multiple version of I.E from 3.0 to 6.0 that run in standalone. Not only has this fixed my problem of accessing applicaitons, it also allows me to test my applications against earlier versions of I.E. Very useful indeed

I often see posts on the APEX forum asking how to implement custom Authentication / Authorisation schemes within their applications. The following is something I have used in several apps over the last couple of years and provides a great base for securing your application. The code base is largley based on the rather excellent article " Storing Passwords in the Database " found here with a few tweaks and changes. Firstly, just to clarify, Authentication Schemes control access to the application and Authorisation Schemes control access to page items / regions and even pages themselves. Implementing your own Authentication I tend to set up an APP_USERS table that stores Username and encrypted passwords that I Authenticate against when page 101 is submitted. All this will be explained in detail as we go. Create the APP_USERS table CREATE TABLE APP_USERS ( USERNAME VARCHAR2(10), PASSWORD VARCHAR2 (255) ); Create the Application Security Package CREATE OR REPLACE PACKAGE app_sec...

For a while now I have had 3 Oracle Homes on my Laptop (Database, 10gIDS and 10gBI Tools) and several more on my server at home. It annoyed me that every visit to a new client site requried configuring multiple tnsname entries in all the Client Side apps. A collegue at work demonstrated how to centrally manage your tnsnames so that all client apps use the same file. Simply save your tnsnames and sqlnet files into a directory on your server (d:\My Documents\TNSNAMES) Change the tnsnames and sqlnet entries in all the clients to: ifile = d:\My Documents\TNSNAMES\tnsnames.ora ifile= D:\My Documents\TNSNAMES\sqlnet.ora And there you go, any changes to the centrally manages tnsnames / sqlnet files will be visible by all client apps pointed to use them.

A while back we had a requirement to generate a lot of heavily formatted Excel type reports out of our application. The users also wanted this integrated seamlessly into the application so that when the report was run, it displayed in the same Internet Explorer window. After a bit of research and plenty of goggling, we came up with this solution (this demo uses a copy of the emp table from the Scott schema): Appologies in advance if the SQL / PL/SQL or HTML format gets a bit messed up but it should compile ok. N.b. I must first point out that this only works in Office 95/2003 and IE. Firefox will generate the report but opens Excel to do so. Please take any of this code and extend it to support Office 2007 and Firefox. It is only meant as a guide / proof of concept. Credit for this one goes to my colleague David Blake as he did the lifting on this one. Create this package (I create this in its own schema and have a public execute but creating it in the schema you are working is...

If you have an HTML page containing only 1 Textbox, standard HTML / browser behavior is to allow the page to be submitted when entered is pressed. Normally I don't have an issue with this but in APEX, no request value is picked up when the page is submitted this way. Consequently, you usually get an error implying there is no page to branch to as the page was submitted without a request value. The solution to this problem is very simple. Create a dummy textbox on the page and in the: HTML Form Element Attributes attribute, enter the following: style="display:none;" Now because the browser thinks 2 textboxes exist, page submission by pressing enter will not occur. Simple solution to a small but annoying problem

If like me you have written several PL/SQL procedures within your APEX app, you will have had to deal with the issue of error handling. As a general rule, I try to hide all Oracle Error Messages from the user and replace these with something more meaningful for example: If a user breaks a unique key by trying to insert the same value twice, the following error message is not uncommon: ORA-00001 Unique constraint violated There is little or no merit rendering this message to a user as those outside the Oracle community, may not be able to deduce what went wrong. We can capture this error and translate it to a user friendly message explaining what went wrong for example: The record you tried to create already exists. Please try again In order to achieve this in APEX, the process is relativly simple. Set up your Custom Error Page 1. Create a new page with the following attributes: Page type: Blank Page Page Number: your choice (for this example I used page 500) Page Alias: ERRPAGE Name:...

In a recent application, we needed to model the Organisational Hierarchy which at its most complex ran to 7 levels deep. This was achieved using a self referencing foreign key (Pigs Ear) similar to that of the EMP table in the Scott schema. In essence, it simply stores the parent / child relationship for each entry in the Hierarchy. This approach serviced the application very well in that a simply tree walk (CONNECT BY PRIOR) allowed us to construct the Hierarchical tree and bounce our requests off that. During the production of the Discoverer End User Layer, it became evident that this Hierarchy needed to be flattened out (un-normalized) in order for it to reported on. This is because Discoverer (or any other BI product) does not support the CONNECT BY and START WITH clause. Discoverer needs to know how many levels exist within a Hierarchy and that every thread in the Hierarchy has the same number of levels in order to build a folder structure that can be reported on. After a bit of i...

It has always been a fairly rudimentary task pivoting a result set so that rows are displayed as columns. More recently I had the requirement to translate a result set the other way so that the columns would be displayed as rows. For Example Your result set starts out like this: SITE COST1 COST2 COST3 COST4 ------------------------------------------------------------------ SITE_ONE 2000 255 SITE_TWO 100 SITE_THREE 145 5000 The desired output should look like this: SITE VALUE ------------------------------ SITE_ONE 2000 SITE_ONE 255 SITE_TWO 100 SITE_THREE 145 SITE_THREE 5000 Thanks to the help from Mr. Tom Kyte at http://asktom.oracle.com I was a...

When your PL/SQL block errors, it has always been a rudimentary task in identifying the error generated using a combination of SQLCODE and SQLERRM. These 2 functions however do not tell us the exact line of code that propagated the error. This feature would be very handy when debugging code. In Oracle 10g, there is a function called: DBMS_UTILITY.format_error_backtrace This will return the line number that generated the error. It does not tell us what the SQL Error message is so I use it in conjunction with SQLERRM to quickly debug problematic code. In a recent application, I used it as part of my error logging and reporting function that allows the support team to quickly troubleshoot errors. The more information about the error we can give them, the quicker they should be able to response. At least that’s the theory. For a more detailed explanation of how to best utilise this function, have a look at this atricle

Well, our week skiing in Meribel came and went too quickly. We all had am awesome time in the "Best British Ski Resort" France has to offer. The Skiing was good (not excellent) but enough to thoroughly exhaust every one of us and our Chalet was very comfortable. Living costs in the Trois Valleys was not cheap however. Beer ranged between 8 and 10 Euros a pint which made for some very expensive nights out. Fortunately, food was included in our Chalet costs. I was devastated parting with 40 Euros for a lasagne, chips and a coke in Courcheval but hey. For anyone interested, some pictures can be found here I shall be putting them all on my website in the next few weeks.

For anyone who has manually written an Update Statement in a Web Application, the issue of Optimistic Locking always took a while to address. The normal convention is to use a series of hidden elements for every form element that is submitted and doing an "old vs new" comparison in the Update statement to see if any valus in the database have changed. I found the main drawback was the need to create large amounts of hidden elements that are then passed into the DML procedure for evaluation. I came across ORA_ROWSCN in 10GR2 that reduces the amount of hidden form elements you need to submit. Its basically a new pseudo column in that database that provides the SCN (System Clock Number for the last time the row was updated) associated with individual rows when they were read. The basic steps to utilise this approach are as follows: 1) create table users (id number, name VARCHAR2 (100), address VARCHAR2 (100), tel NUMBER) ROWDEPENDENCIES / ROWDEPENDENCIES need to set as SCN by de...

A while ago I came across an issue exporting the contents of a BLOB to a flat file on my file system. My processing was to upload a .csv file into the wwv_flows_files table, and write a file back to my Oracle directory using the UTL_FILE api. Upon inspecting the file produced, a newline character was inserted after every line. To cut a long story short, a colleague of mine came up with a workaround that involved parsing the newley produced file, removing any spurious lines and creating a new file minus the blank lines. As you can imagine, for large files, this added a fair amount of time to the Upload process. There is light at the end of the tunnel however. This article was sent to me by another colleague. At the end there is a solution to the problem. Cheers Kris " Oracle 10g includes extra open modes (rb, wr, ab) to signify byte mode operation. The "wb" open mode can be used along with the PUT_RAW procedure to prevent extra newline characters being added on a Wind...

The strangest thing happened the other day whilst performing an application import within Oracle XE. The import was successful but running the application however was less so. Some of my pages contained a lot of JavaScript in the HTML Header section of the Page Attributes. On some of these pages, the import appeared to add a carriage return to one of the lines and thus breaking my script. The knock on effect was that my application ground to a rather unimpressive halt. Without having the time to investigate why, I decided that it would be better to store all my JavaScript in a . js file on the server and write a reference to it on the pages where the scripts were needed. Once you have created your . js file (I store in a sub directory if the /i/ path) on the server, you can reference these files by adding the following to the HTML Header of your page: Storing your long JavaScript externally to your application also makes debugging a bit easier as well.

For those web developers out there using FireFox ,you have to install the Firebug extension. It lets you explore the DoM in far more detail than ever before. Simply download and install the extension, restart FireFox and hit F12. You can then see all your HTML source, JavaScript, CSS etc. It also has a cool debugging tool for JavaScript that lets you step through line at a time. I can't tell you how much time this extension has saved me this year. Get it, install it and give it a whirl. You wont be disappointed I promise. Microsoft have released their Developer Toolbar for Internet Explorer which goes some way to providing the same functionality for the Microsoft die hards out there but its a million miles away from our friends over at Mozilla.

Well, finally made it into work on the 3rd January and it's always tough after the holidays. However its not long to wait until the 13th January when I will be spending a week in Meribel. Hopefully we will all return in one piece this year as well. My girlfriend had quite a nasty accident last year in Austria which resulted in a large laceration to her leg and required several rather large stitches. I digress however and just wanted to offer some advice for those who are struggling with the "back to work" bug doing the rounds. A colleague of mine told me yesterday that his girlfriend always has to have a holiday to look forward to. I thought this was a brilliant way to beat off those New Year Blues and so why not start planning your Easter getaway. Surly the prospect of another holiday is the best way to cure the disappointment of one just ending......... Just a thought.