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, 
                   when_button_pressed, 
                   authorization_scheme)
         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.