Skip to main content

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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!

Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…