Oracle's Data Dictionary: The Holy Grail

Well, I just read through my blog, and it appears that I have gone woefully astray.  I promised (in September of 2009...ouch!) to provide a monthly series called Banner tips of the month.  Alas, my goal has been completely forgotten, never fulfilled.  So, here I am again to see what I can do to remedy the situation!  Starting off, with January's "tip of the month" is a post on using Oracle's Data Dictionary.

Terms and Definitions

It surprises me when I go visit a client and their analysts haven't heard or rarely use Oracle's Data Dictionary (ODD).  Wikipedia defines a data dictionary as a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format." (http://en.wikipedia.org/wiki/Data_dictionary)  Another important term commonly used in conjunction with data dictionary is "meta data".  Meta data is simply data or information about data.  One of the most commonly used forms of meta data in the world today is the a library's card catalog, where information about the entire library system inventory can be found, categorized, and searched.  Those three words (especially the last) are critical to a good data dictionary.  In any case, back to the main point: using the ODD, we can quickly discover many interesting things about our Oracle database.  But more important than learning about the database itself, is the fact that we can glean valuable information about our Banner system from the ODD!

Here is a short, real-world, example.  I received an email today (the inspiration for this post in fact), requesting any information I may have regarding a function that may help him utilize their CAPP setup more effectively.  After thinking about it for a few minutes, I started typing an email to him indicating that I really didn't have much to aid to give in regards to his specific question.  However, after thinking about it a second more, I thought I'd do a quick search for some possible solutions using.....you guessed it, the ODD on my Banner 8 installation!  After 5 minutes spent querying the dictionary, I found several possible leads to the answer for which he was searching!  So, you're probably wondering, "what was it he was looking for!?".  Alas, I'm not going to spoil the surprise.  I will, however, give you several useful queries and tidbits about the ODD that may come in handy in your own searches along the way.

First, more specifics on the ODD.  The ODD is a series or Oracle views that contain data about the oracle system.  This includes data on all of the views, functions, indexes, packages, tables, procedures, users, and any other database related objects you may care to know about in your system.  So, if we adapt this concept to banner, when we install banner on oracle, the ODD then collects information about all of banner's tables, functions, views, and so on.  So, the next logical step would be to query those ODD views to find the information we need about those stored objects.  So, here we go.

Querying the ODD

The first thing we need to know is the name(s) of the ODD view(s) we are going to query.  For a complete list, you can visit http://www.oracle.com/pls/tahiti/tahiti.catalog_views.  The ones I use most often include the following:

• all_tables
• all_views
• all_objects
• all_source
• all_columns
• all_col_comments
and
• all_tab_comments

Interesting that they all start with "all". There is, in fact, a reason for that, but it's not too important for the purpose of this discussion. If you're interested, you can read more about the ODD at the site I linked at the start of this paragraph. In any case, let's look at a reason you may want to query some of these views.

Let's consider a situation in which you don't know where a piece of data resides within the database (or perhaps IF it even exists at all). The first logical place to look would be all_tables OR all_tab_comments. The reason you would pick all_tab_comments is because you may not know what the name of the column or table is so you can't check all_tables. So, let's say we're looking for a column that contains data about transcripts. Here is a simple query that would return some interesting facts about tables that pertain to transcripts:

select owner
,table_name
,comments
from all_tab_comments
where lower(comments) like '%transcript%';


Now, I'm not kidding when I say that query (on my system) returned no fewer than 37 tables that had comments related to transcripts. However, notice that we also have an owner column as well as table_name and comments. So we could refine the query a little more if we knew or could guess something about what we're looking for. For example, with transcripts, there's a good bet that we only want STUDENT tables, so we could filter on the owner (SATURN) or on the table name (like 'S%' as follows:

select owner
,table_name
,comments
from all_tab_comments
where lower(comments) like '%transcript%'
and table_name like 'S%'
and owner = 'SATURN';


Now, this was a little redundant, I know, but hopefully it got the point across!
So, as you can see, using the ODD to find data location is very handy! Another common practice that is VERY useful and VERY RARELY used is using the data dictionary to find functions and procedures to aid in your reporting and application development. SunGard has spent a great deal of money (well, SCT did really) developing the Banner product and it's myriad of PL/SQL APIs, we may as well use them! Here is a query that could help you find a function to calculate age:

select owner, object_name, object_type
from all_objects
where object_type = 'FUNCTION'
and lower(object_name) like '%age%';


On my system, this query resulted in 8 different functions with the letters 'age' somewhere in the name of the function. However, only 1 function was called: F_CALCULATE_AGE. Now, that only tells us the name of the function. If we wanted to see it's guts to determine if it's something we can use, we could write the following query:

select line, text
from all_source
where name = 'F_CALCULATE_AGE'
and type = 'FUNCTION'
order by line;


This query results in two columns, one that contains the line number for every line in the function code and the other column contains the actual text for the function itself. This feature is invaluable when trying to determine if the function will be useful for whatever problem you're trying to solve.

So, the last thing I want to leave you with is an question.  What if we were to build a DataBlock or two in Argos that "parameterized" a few of these queries and made them available to some of our power users?  Happy Hunting!!!