Mining Application Express data dictionary views: find unconditional processes

I ran into a problem yesterday on the Oracle Dev Gym (offering quizzes, workouts and classes on Oracle technologies). A number of rows of data were incorrectly deleted. I was able to use Flashback Query to restore them (thank you, thank you, Flashback Query!). Crisis averted. 

But how did this come about?

I recruited Chris Saxon to help me figure out how this could have happened. In relatively short order, we narrowed down the culprit to a process in the Dev Gym Application Express definition that was unconditionally "removing previews" - but was in fact removing all rows, "previews" or not. Ugh.

So we fixed that.

But it got me wondering and worrying: what other processes in my app are unconditional? And should they be?

While some processes fire unconditionally on a page (for example, to get the data from tables and display them on the screen), many are (or should be!) restricted to a button press, the result of a conditional expression, or an authorization scheme.

And for sure if any of these have the word "remove" in them, I want to review them carefully to make sure I am not causing more and future angst.

So how can I find all the unconditional processes in my application? Sure, I could do some research. Or I could be lazy and ask my friends on Twitter. So I did, and Dimitri Gielis responded almost immediately:

Thanks, Dimitri! 

I don't always like Twitter, but when I do, it's because of how it's become a great way to get questions answered quickly.

Now, as you probably know from personal experience, the first idea/solution someone has doesn't necessarily make it all the way to the end. That was the case here. From my perspective, a process is unconditional if it has no:
  • Server side condition, whether PL/SQL code or client-side JavaScript (as in, "Item = Value')
  • "When Button Pressed" set
  • Authorization scheme
So in the end, my query became:

SELECT page_id, process_name
    FROM apex_application_page_proc
   WHERE COALESCE (condition_type, 
         IS NULL
ORDER BY page_id

Hopefully you can find that useful as well. But what I wanted to focus on in this post is: how did I know the names of columns to reference in the query?

You could try to find descriptions in doc, but an even better way to get your answer is from within APEX Application Builder itself.

Click on Workspace Utilities:

Then Application Express Views:

Then use the handy interactive report to find your view:

Then either build and save a report right inside Application Builder or find the columns of interest and roll your own, as I did:

I am certainly a rank amateur when it comes to knowing about and leveraging the APEX views. Making them accessible inside Application Builder itself, and leverage APEX features to make it easier to query those views, is a brilliant move. Just brilliant.


  1. Nice :)
    Based on your previous blog, aka "admiration for UI developers", I think that you are strongly on your way to cross the "Rubicon" from the (old-fashioned ?) back end database development to the (bright, yes ?) UI development :)

    Regarding the UI development paradigm that "allows" things to happen with/without condition ...

    This reminds me of the somewhat similar paradigm used by Oracle Forms, in the sense that
    the most and the most subtle part of the programming effort had to be put in preventing the user to do the (practically so many) "wrong things", rather than programming the (few) "good things" that the program ultimately had to accomplish.

    As I said so, so many times, APEX looks to me excessively complex, even frightening ...
    maybe just for the "old-fashioned" developer that I still am ...

    For me, for sure, looking up that APEX view definition in the database (and most probably also in the documentation/reference) would have been much more natural than knowing where to look for it inside APEX ...

    I think that, however, the tendency of today's world is towards "simplifying life", in the sense
    of "let the user do anything he wants in the UI, and only stop him on anything wrong he might have done when he asks for this explicitly", for example, by pressing a button and thus passing control to the application itself, which has its well-defined flow of checks and actions,
    probably best encapsulated in an API.

    I slightly remember that even "heavy and serious" software products like SAP do rely on
    these two "phases": a phase when "the user works", and a phase when "the program works".

    It is indeed easier for the UI developer to work under such a paradigm, rather than finding the exact "point of check" that the user cannot avoid, whatever he does ...

    I think that there is also a psychological aspect to the whole thing ...
    I personally, as an end-user, usually don't like so much when a software "pretends to guess"
    what I want to do, and tries to do things automatically for me, beyond my control, and usually does them wrongly and/or at the wrong time point. Maybe the best example are the Microsoft Office programs ...

    Well ... just a few ideas that "traveled my brain" reading this post ...

    Thanks a lot & Best Regards,

  2. Iudith, I and I am sure many others greatly enjoy the journeys your ideas take through your brain - and out!

    I feel that overall the trend towards simplification is a fine thing, but I am sure humans will take it too far, get too excited about ML and AI figuring everything out for us, often wrongly or embarrassingly.

    APEX and excessively complex: yes, lots of bells and whistles, toggles and switches. But as with any tool, once you get a basic orientation, most of that becomes invisible (for better or worse) and you just see the pieces you need/use.

  3. Hello Steven,

    A propos ML and AI ... Among the sad news of this week related to the passing away of Prof.Stephen Hawking, I just saw on the web a mention that he did strongly dislike these ... thinking of the bad "developments" such tools are able to bring about and ultimately the catastrophes they might cause to human life.

    He was really a rare genius, and a very far looking one :)

    You see ... we, developers, do live inside our little boxes, garnished nicely with those bells and whistles, and forget about the really big world (and not just big data !) around us ...

    Regarding APEX and my feelings about it: yes, that is exactly what I am missing: a good fundamental knowledge about the overall logic of how this product works,
    aka, the "basic red thread" of how an application works.

    The many texts that I have seen up to know do jump far too quickly into all those bells and whistles, forgetting to explain first the complete set of basic knowledge.

    It would be nice if one of the (many today) APEX experienced developers would take the challenge to elaborate a book specifically oriented towards "the minds of Oracle Forms" developers, and explaining the development concept in light of the way of thinking (or "brain pattern") an Oracle Forms developer already has.

    Of course, it should be one of those former experienced Oracle Forms developers who had "migrated himself" to APEX, so he knows exactly where the APEX critical understanding points are for an Oracle Forms developer.

    But ... the world is running too fast ... and newly born "baby developers"
    already do not remember the good older tools of the past ...

    Thanks a lot & Best Regards,


Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Quick Guide to User-Defined Types in Oracle PL/SQL

Get rid of mutating table trigger errors with the compound trigger