APEX Evangelists Network Welcomes Paul Broughton

As I recently blogged, APEX Evangelists is undergoing a transformation (stay tuned for what that means for me).

We are very happy to announce that Paul Broughton (Twitter - @paulbroughton) has joined the AE Network!

For those of you who don’t know Paul, he started working for APEX Evangelists back in July 2009 (wow time flies!) and has been involved in many client facing projects (and very recently was presented with an award from a very happy client!).

Paul has grown as an APEX Developer and has been a valuable member of the team year on year and I’m sure he’ll continue to do so.

Congratulations Paul and welcome to the AE Network!

The evolution of APEX Evangelists

Every year we at APEX Evangelists come together to discuss the past and the future of the company.
In the past 7 years a lot has changed; the APEX market changed, the community changed, we personally changed, …

We really like APEX, we are all passionate about it, we love to have technical discussions with each other and come together to share our thoughts, but we also like our independence and make our own trade-offs company wise.
So we decided to go a different route with “APEX Evangelists”. And that’s why we’re now in the midst of transforming APEX Evangelists from a company to a network.
A network of APEX experts. A network without any financial incentive behind it. A network of independent people that are passionate about APEX. A network of recognised experts in the Oracle community. A network with members that can rely on each other and trust each other.

You can also become part of this network, but only by invitation and acceptance by the other members.
We love to compare us with the Oaktable network, which is a network of experts for the Oracle database. We hope that “APEX Evangelists network” will be the same for Oracle Application Express.
The first addition to the APEX Evangelists network is Martin Giffy D’Souza and we hope to grow the network further in the next months.

The new website of APEX Evangelists is live which reflects the network now, you find it at http://www.apex-evangelists.com

If you wondering what else the future will bring me, stay tuned or drop me an email at john.scott@apex-evangelists.com

ODTUG APEXposed 2012

What more is there to say? This event is going to be fantastic and there’s still time to register!

ODTUG APEX Plug-In Competition

As Martin has already announced, there is a very exciting APEX Plugin Competition hosted by ODTUG.

Here’s the official announcement (taken from the ODTUG page):

ODTUG is holding its first ever world wide APEX Plug-ins competition! This is your opportunity to write an APEX plug-in and have it viewed by the entire international APEX community and maybe crowned 2012 ODTUG APEX Plug-in Developer of the Year! Even better, there are some really great prizes with the grand prize being a free pass to Kscope13 or a Jawbone Jambox!

This is an opportunity for you to not only win some cool prizes but also to show just what is possible with APEX plugins (and don’t worry if you’re a beginner in APEX, there is a separate Experts category to make sure that everyone is judged as equally as possible).


APEX Quizzes Start on 18 June 2011

With the growing popularity and usage of Oracle Application Express, we are very excited to announce a new weekly Oracle APEX quiz launching on Saturday, 18th June. Weekly quizzes start on Saturday and end on Friday at midnight (UTC).

We have been deeply involved in the development (and success!) of the PL/SQL Challenge from the very beginning….and we have now taken the next logical step:

APEX Evangelists is now sponsoring a weekly APEX quiz, so you can now look forward to me and Dimitri Gielis of APEX Evangelists challenging you with some interesting quizzes. If you’re already a seasoned APEX developer or you are only just beginning your journey with APEX, then this is the perfect time to start competing in the APEX Quiz to help to deepen and broaden your knowledge about one of Oracle’s most exciting application development tools.

To play the APEX quiz starting 18 June, simply visit http://www.plsqlchallenge.com and choose the quiz in the Play a Quiz table. We also encourage you to submit your own ideas for APEX quizzes – and to volunteer as a reviewer (which you can do through the Feedback page).

We would like to see more developers playing the APEX quiz than the daily PL/SQL quiz (hey a little healthy competition is a good thing right?!). So help us spread the word! Let your friends co-workers know about the APEX quiz!

Expert Oracle Application Express

Expert Oracle Application Express

It’s been around 3 years since I wrote Pro Oracle Application Express (with a chapter contributed by Scott Spendolini), and like most first time authors I said “That was a great experience, but I’m in no hurry to write another book again!”.

Writing a technical book is a very rewarding experience, but trying to do it alongside your regular day job is tough (tougher than I ever imagined). Trying to meet publishers deadlines while still trying to  keep your (paying!) clients happy is very tough indeed.

However, a couple of events made me rethink my “never again” policy, firstly the very sad passing of Carl Backstrom and then the equally sad and unexpected passing of Scott Spadafore.

These two people helped me immensely during my time with Oracle Application Express and since I knew them both personally I wondered what I could do to help the families of two people who I admired immensely and also considered friends.

That’s when I came up with the idea of writing a collaborative book, with each chapter written by a different author who knew both Carl and Scott.

At ODTUG last year I approached people who I knew felt the same way about Carl and Scott as I did and I felt an enormous sense of pride that every single one of the authors didn’t hesitate in agreeing to contribute a chapter, so for that I am eternally grateful to:

There is some great technical content in this book, written by some of the worlds best known Oracle APEX experts. Even if you don’t think you need this book, buy it…PLEASE.


A PLEA FROM ME: If you are only going to buy 1 book on APEX this year…please make it this one, if you are going to buy more than 1…well make sure one of them is this book!

APEX 4.0 released

APEX 4.0 just got released, this is the BIG one for APEX developers, we’ve been anxiously awaiting the release (and a few bets have been known to placed on when exactly it would be released!).

More information is available on Joels blog, and you can download APEX 4.0 as always from the main OTN APEX page here

PL/SQL Challenge – Goes Live!

The PL/SQL Challenge website has just PL/SQL Challenge - Home gone live!

If you haven’t heard about it yet, it’s a website where you can pit your wits against a daily question by none other than Steven Feuerstein (yes that Steven Feuerstein!) and Finn Ellebaek Nielsen. Wait it’s even cooler than that, you can win cash (and other!) prizes and if that’s not enough to tempt you then you can see how you rank against other PL/SQL developers all over the world (and who wouldn’t want to know that!).

Steven asked APEX Evangelists (what seems like a long time ago – before we formed Sumneva) to help him create the front-end for the PL/SQL Challenge website (we previously created the I Love PL/SQL site for him) and we (myself, Dimitri Gielis and Paul Broughton) have worked in our ‘spare time’ to help to bring the site to life.

We of course used Oracle Application Express to create the front-end application (all of the back-end is PL/SQL code, what else did you expect from Steven?!), and even though I’m biased I have to say this makes for a dream development environment.

I have to say it’s been a pleasure to work with two professionals such as Steven and Finn. It really has been a baptism by fire for Paul working on such a project (how many interns get the chance to work with such giants as Steven?) but he more than rose to meet the challenge, thanks Paul!

So what are you waiting for? Go along…sign up…play the quiz…and see how well you can do (someone is going to win those prizes!).

Application Express 4.0 – Early Adopter 2 Now Available


If you didn’t already see the news, the EA2 release of APEX4.0 has just been opened up.

Head over to www.tryapexnow.com, sign up for a workspace and try out all the cool new features.

EA2 includes the following key features -

  • Websheets (this will be huge!)
  • New collection enhancements
  • Tree region
  • Debugging has been rewritten

and many more (see Joel’s blog post for a full description).

APEX 4.0 is going to be a huge release and will have (arguably) the biggest impact of any APEX release, it’s certainly interesting times being involved in the APEX community.

Views and Order By

Developers like views…they can simplify things and hide (protect us from) underlying complexity.

For example, would you rather write the query -



  emp e,
  dept d
  e.deptno = d.deptno;

It’s a rhetorical question so please don’t say “I prefer the second”…otherwise there’s no point me continuing this post. Anyway, that’s quite a simple example, imagine the view hides all sorts of complexity (analytics, subquerys etc).

The view gives us a single ‘point of truth’, we can tell developers “use that view, it contains all the information you need, you don’t need to understand all the logic yourself”, views are a very powerful tool for code re-usability (and simplification).

However, what if someone codes a view like this -

  emp e,
  dept d
  e.deptno = d.deptno
ORDER BY d.dname;

Notice the order by there? They probably/possibly created that view to use directly in a report, adding the ORDER BY into the view so they could just write -

SELECT * FROM empdept_vw

rather than

SELECT * FROM empdept_vw edv ORDER BY edv.dname

Well that’s fine, but what is someone else later on uses that view in this way -

SELECT * from empdept_vw edv ORDER BY edv.sal DESC

Well we’re now potentially making the database do more work because the query is sorting the data in a different way to the view sorts it (so therefore the sorting in the view is essentially redundant). The key point here is that the database cost based optimizer might be smart enough to figure out what you’re doing and eliminate the redundant sort, however there might be lots of cases where that doesn’t happen (consider the scenario where you have views built on top of views, with ORDER BY’s thrown in there at a deep level).

The cost-based optimizer gets smarter with every release of the Oracle datbase, however it will never (and I’m reasonably confident about this!) understand your data and requirements as much as you do (or rather as much as you should).

So, I would argue anytime you see a view with an ORDER BY in it you should really question why you’re doing that (rather than putting the ORDER BY in the query that uses the view). We can even make our job easier and look for any views that have an ORDER BY defined in them by querying DBA_VIEWS or USER_VIEWS. Unfortunately those views contain the text definition of the view in a LONG column rather than a CLOB, which means we can’t do a simple LIKE predicate -

JES@dbtest> select * from user_views where upper(text) like '%ORDER BY%';
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

We can get round this in a number of ways (such as creating a copy of the DBA_VIEWS or USER_VIEWS in a local table converting the LONG column to a CLOB etc), or we can use a different approach like this -

JES@dbtest> select
  dbms_xmlgen.getxml('select text from user_views where view_name = ''' || view_name || '''') like '%ORDER BY%'

(note unfortunately I can’t remember who to credit with the DBMS_XMLGEN trick above, I saw it a long time ago and it’s been lost in the mists of time).

and yes…before you ask…in the schema I ran that query in I did find a view that had an unnecessary ORDER BY in ;)

I’d be interested to hear your thoughts on if you find ORDER BY’s in views a problem/issue/area-of-concern, or if it’s just me?