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