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, 
                   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.

Comments

  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,
    Iudith

    ReplyDelete
  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.

    ReplyDelete
  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,
    Iudith

    ReplyDelete

Post a Comment

Popular posts from this blog

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 p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel