Anthony Rayner

Subscribe to Anthony Rayner feed
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.Anthony Raynerhttp://www.blogger.com/profile/04412495232341508437noreply@blogger.comBlogger34125
Updated: 4 hours 38 min ago

UKOUG - Day 2

Mon, 2007-12-10 05:34
It had finally arrived, Tuesday 4th December. A day that had been engrained on my brain ever since receiving 'UKOUG 2007 - Speaker Confirmation' in my Inbox back in July. The day of my first big presentation, 'Building The Rich User Interface with Oracle Application Express and Ajax'.

So very nervous I headed down to Birmingham from Reading and arrived at the ICC at around 9.30am. I was speaking at 11.15 so gave myself a bit of time to register and go over the slides one last time. A worthwhile activity as I was soon to realise on discovering 3 of my slides were missing which I must have deleted the previous night. Thank goodness for backups!!

I headed up to Hall 8b, the venue for my presentation. It was a good size, around 170 capacity but didn't seem to big so I was ok with that. There was no session in there before me so had plenty of time to get setup and settled, or so I thought. The technician arrived soon after me and asked if I was going to be using the supplied laptop or my own, I said my own and he worringly replied, 'You are the first person who's wanted to use their own'. I replied 'Is that a problem?', and he assured it wasn't but there would just be a bit of setup to get it working.

Time went by, people started arriving and my opening slide was still no where to be seen on the big wall behind me. The room of around 50 people was filled with silence, Harrison my session chair did a good job of trying to break it with a quick poll of how many people had used APEX before, which was most (but not all) of the room. I then ran through my agenda which seemed like it took about 5 minutes but probably only lasted about 30 seconds. Anyway, with numerous trips back and forth to the control room by the technician, my opening slide finally appeared on the big wall, a sigh of relief from me and after all the waiting and anticipation, I could talk.

It went ok. I know there are a few things I could have explained better and in more detail. I had always been worried about the amount of technical information I was trying to get across in 1 hour (although originally I was worried I wouldn't be able to fill it!) and in hindsight, I think I should have gone for longer.

So having finished talking in about the right time, there were a few questions and the job was done. John Scott came up to me afterwards and introduced himself. It was great to finally meet him having only known of him before. He said my presentation was really good, which means a lot coming from someone like John and also suggested that I should have a longer time slot to get through everything, I definitely agree.

Me, full of relief!

For anyone who is interested in getting the slides I have currently taken them offline just because I wanted to change a few small things. Once these changes are made I will post a link on my blog. I am also trying to get a demo up and running which will have all the code available to download. Watch this space!

Having done my presentation, and with the weight of an elephant lifted from my shoulders I was then free to enjoy some of the conference.

Really enjoyed Zory Zeharieva of CERN present, 'A Real-Life Experience of Rapidly Building Web-Interfaces with Oracle Application Express at CERN'. She presented well and covered some best practices for building scalable applications which was very interesting and also expressed some concerns around the way APEX application files are managed within versioning systems, a common concern which came up in a few places over the conference.

Also then dropped in on Oracle's Barry McGillin talking about, 'Consolidate Your Microsoft Access Applications to Oracle Application Express'. Barry is a great speaker, informative and easy to listen to (even at the end of a very long day!). He showed how to migrate an Access database with data into an Oracle database and then use the APEX 3.0 feature 'Application Migration Workshop' to build your APEX application. Well worth a look for anyone planning on doing this kind of thing.

So the day of presentations was over, and it was up to the free bar to relax and catch up with some people. Had a very pleasant drink with some colleagues and headed back to the hotel for a good night's sleep.

UKOUG day 2 done and dusted. The months of worry and preparation were worth it and my life can now resume normality. Presenting is, although nerve-wrenching and time-consuming, a very rewarding experience and I would recommend it to anyone.

Categories: Development

Problem with 'Not Null' validations

Sun, 2007-12-02 04:31
Introduction...
A question popped up on the forum this week about the ability to bypass not null validations. The problem is if you define a 'Not null' validation on a page item, the user can enter a blank space and the validation will allow it. But there is a simple workaround and it requires no changes to any of your existing validations.

Note this will only work in APEX version 3.0 or higher. If you are looking to implement this on 2.2 or 2.2.1, then please refer to the link above to the forum post where this was discussed, where I posted a solution for 2.2 (the views were changed slightly). Unfortunately, pre 2.2 this method is not possible, as I'm referencing the APEX dictionary views that were introduced in 2.2.

How...
To get around this you can use an APEX application process to trim all the items for the current page that have associated not null validations. Create the following application process:

Sequence: 1
Process Point: On Submit: After Page Submission - Before Computations and Validations
Name: TRIM_NOT_NULL_ITEMS
Type: PL/SQL Anonymous Block

Process Text:
BEGIN
FOR cur IN
( SELECT items.item_name
FROM apex_application_page_items items,
apex_application_page_val vals
WHERE items.application_id = TO_NUMBER(:APP_ID)
AND items.page_id = TO_NUMBER(:APP_PAGE_ID)
AND items.item_name = vals.associated_item
AND vals.validation_type
= 'Item specified is NOT NULL'
)
LOOP
apex_util.set_session_state( cur.item_name
, TRIM(v(cur.item_name)));
END LOOP;
END;

I added this to a form on EMP and setup a not null validation on the job item. Then loaded the page, keyed a space for the job field and on inspection of the debug, you can see that before the process fires, session state shows a " " for P12_JOB, which would have passed the not null validation.



And then after the process has fired, it has been trimmed and set in session to "", which causes the not null validation to correctly fail.



Hope it helps,
Anthony

Categories: Development

Debugging with the APEX repository

Mon, 2007-11-19 06:56
Introduction...
I was recently working on an APEX application on a familiar 'Form on a Table' wizard-built page and encountering a ORA-00957 Duplicate Column Name error, meaning that a column name must be specified more than once in the INSERT.

So the problem must be that I had more than one page item bound to the same database column. This page had got quite bloated with lots of other business logic so I started by turning off regions, the old 'process of elimination', debugging through seeing if there was anything obvious, but nothing sprang up. Then I thought if only there was a way to view all the page items on my page which were bound to the same database column. Well there is, using the APEX repository.

For those of you who are not familiar with the repository, it is basically a set of views that expose all the APEX application metadata. For further information see Have a clean up, utilising the APEX repository which contains links to loads more information.


How...
In the repository there is a view called APEX_APPLICATION_PAGE_DB_ITEMS which is what we are interested in. It is described in the APEX_DICTIONARY view as...

'Identifies Page Items which are associated with Database Table Columns. This view represents a subset of the items in the APEX_APPLICATION_PAGE_ITEMS view.'

We can then run the following query to return all the items for a specific application / page bound to a db column more than once.
SELECT   db_column_name,
SUM(1) Duplicates
FROM apex_application_page_db_items
WHERE page_id = :page_id
AND application_id = :app_id
GROUP BY db_column_name
HAVING SUM(1) > 1
ORDER BY SUM(1) DESC
(Note: This view does not contain conditional rendering information, so if this was required you would need to join to APEX_APPLICATION_PAGE_ITEMS on ITEM_ID).


Conclusion...
So if ever you think, I wish I could see this information about this page / report or whatever, you probably can, just take a look into the repository. I wonder if there would be any scope for an APEX debugging framework that defines sets of processes linked to common 'ORA' errors. So for this example, it would simply be:

Error: ORA-00957 - Duplicate column name
  • Step 1: Run the following query, binding in your page and application id.
    SELECT   db_column_name,
    SUM(1) Duplicates
    FROM apex_application_page_db_items
    WHERE page_id = :page_id
    AND application_id = :app_id
    GROUP BY db_column_name
    HAVING SUM(1) > 1
    ORDER BY SUM(1) DESC

  • Step 2: Investigate all rows returned from the query and unbind items which should not be bound to the database column.

  • Step 3: Retest your page.


That would be nice.
Anthony.

Categories: Development

Survey results

Mon, 2007-11-19 06:53
Following the recent survey I conducted on this blog, 'What would you like to read more about on my blog?', the results were:

Developer Tips - 54%
AJAX General - 45%
AJAX with JSON - 45%
BI Publisher Integration - 33%
Access Migration - 0%

I will thus be focusing my efforts on Developer Tips and AJAX related posts in the near future. I was quite surprised at the 0% interest in 'Access Migration' and would have thought this was be quite popular as this is one of the main platforms systems are built on that APEX applications replace. Or maybe it's just that developers aren't using the migration functionality built in to APEX / SQL Developer and just doing it without looking at these. Interesting.

Thank you all for your feedback. More to come shortly.
Anthony.

Categories: Development

What do you want to learn about?

Mon, 2007-09-24 07:14
In the interest of trying to write good posts that the APEX community will find useful, I have added a new 'poll' feature to my blog. My first poll is entitled simply, 'What would you like to read more about on my blog?' and I have suggested a few titles to pick from. You can cast your vote by selecting from the check boxes on the right of the page, below 'About Me'.

If you have any other suggestions or areas you wish to learn about or understand better, then please add a comment to this post.

Look forward to hearing from you!
Anthony.

Categories: Development

UKOUG 2007 agenda packed with APEX

Wed, 2007-09-05 10:43
The agenda for UKOUG 2007 has just been released and there is currently a total of 13 presentations on or relating to APEX (and a keynote delivered by Tom Kyte!). They are...

Loads to learn so come along, I'll be there and presentating 'Building The Rich User Interface with Oracle Application Express and AJAX' so looking forward to doing that (scary!) and listening to many more! Hope you can make it!
Anthony.

Categories: Development

Pages