The APEX Data Dictionary

One of the APEX features I find myself using frequently is the APEX data dictionary. I’ll get to “Why?” in a moment–but I feel like I need to answer the “What?” question first, since I’ve talked to a fair number of developers who either don’t know about the data dictionary, or only have a passing familiarity with it.

At the highest level, the data dictionary is a collection of views the APEX team has provided which give visibility to the underlying meta-data about all of the APEX applications on an instance. Depending on how your system is set up, you may have visibility to the meta-data for all applications, or potentially just your workspace (which is the case on apex.oracle.com, for reasons I hope I don’t need to explain). The meta-data is, for the most part, everything that you as a developer can change about your applications, websheets, workspace, and stuff in team development. I have run into some times where the information I needed wasn’t available in the data dictionary, but almost everything is in there…somewhere. Finding it can be a challenge.

Technically, the data dictionary isn’t really anywhere, since it’s just a set of views. But you can find a user interface for those views by opening an application in the Application Builder and selecting the Utilities icon. In the bottom right corner of the next page’s main region is an icon labelled “Application Express Views”–that’s the data dictionary; just not very well named, in my opinion. Clicking that icon takes you to an interactive report of the views, which is useful if you’re pretty sure you know what you’re looking for, but if not, it can be a bit overwhelming.

The APEX data dictionary tree viewLuckily, the APEX team realized that a report of 117 views (as of the 4.2.4 release), with the inherent limit of 30-character long view names, is a bit cumbersome and difficult to navigate. So in addition to the interactive report, they provided us with the Tree View, which you can see here. The tree view organizes all of the data dictionary views into a sensible hierarchy–all views dealing with regions are listed under the view for pages, which is under the view for applications, which is under the main workspace view. So if you’re looking for a specific view but don’t know what its name is, you can find it by thinking about the rendering path that the APEX engine has to go through.

If you want, you can continue using the Application Express Views utility to query data out of the data dictionary, but I almost never bother with that; I find that it is much simpler to just write my own SQL. Incidentally, the application-related views will return data significantly faster if you provide them with values for workspace and application_id, especially if you have a large APEX instance.

Bet you thought I’d forgotten my promise to explain why I use the data dictionary so frequently, hadn’t you? Really, the answer is pretty basic: we have roughly ninety applications in our main workspace–all of the released packaged and sample applications, as well as several in various stages of development, and also some random test applications. And we often come up with improvements which we want to apply universally. Keeping track of what has and has not been done can be very cumbersome–but with the data dictionary, we can write queries that tell us exactly what we still need to work on.

Comments

  1. Stew says

    David,

    I’ve used these views on occasion but I really appreciated your answer to “Why?”, because I’d never considered looking for objects that need to be changed this way. It certainly gets around the inability to search through the source code in a robust way, for example, because query strings are concatenated together and may split up the string you’re searching for.

    Thanks!

    Stew

  2. says

    David,
    I’m a little late seeing and responding, but I could not agree more; the Oracle data dictionary (including that of the APEX world that you mention, David) is an invaluable resource. As a DBA for many years, I came to depend upon the data dictionary (select * from dictionary; – for an informative listing of dictionary views) to solve questions my users (and myself!) had about Oracle objects. Thank you for your posting on these APEX views…we’ll all benefit from using this resource, which will continue to mature with each release, thanks to the APEX team!…and to you, David, for advertising the resource!
    Dave Bender

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>