Drillable Reports

Sue blogged about how some reports have drill thru ability to get the underlying object. This rasises the question of how can I do that to my report which lists my most interesting objects?

The answer to this is very simple. Here's an example which is simple query of which object were altered most recently and makes it a drill thru.


This SQL run in the worksheet gives you a list of object.
select owner,
object_type,
object_name,
last_ddl_time
from all_objects ao
where owner <> 'SYS'
order by last_ddl_time desc;

Instead of then finding your way to that object in the tree let's make it drill thru. This is done very easily by adding a few columns.
select owner,
object_type,
object_name,
last_ddl_time ,
owner sdev_link_owner,
object_type sdev_link_type,
object_name sdev_link_name
from all_objects ao
where owner <> 'SYS'
order by last_ddl_time desc;


The fist thing that is obvious will be that these columns will not show in the results. Now when the query is run and a row is double clicked you will be taken to that object. The only catches are it has to be an object which is supported ( basically see the tree ) and you must have access. Before anyone askes there is currently no way to go to tab #X of the object.

I'm sure someone can make better use of this for things like my largest object report or maybe a report on V$DB_OBJECT_CACHE

This can be used in the worksheet or in the sql of a report.