The Connection Tree and Schema Browser make pretty nifty object navigators. But maybe you want your own custom list of objects.
Perhaps you’re working on a project and you only want to see objects that pertain to Project XYZ. And you’re able to ID these objects by a common column ID – or use your imagination and think of some other meta bit or byte.
It could look a little something like this:
I simply made a user defined report, and docked it where my connection panel is.
The not so simple bit, I wrapped the object name, object type, and schema bits with some custom TEXT that SQL Developer knows to render as an object navigator link/URL.
The important bits…
SELECT owner SCHEMA, 'SQLDEV:LINK:' ||owner ||':' ||object_type ||':' ||object_name ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' TABLES FROM DBA...
If I refresh the report, it will prompt me for the text I want to match tables on, in this case, I’m looking for columns from DBA_TAB_COLS where COLUMN_NAME like :TEXT.
In the screenshot, I’ve supplied %EMP% to find any table in the database that has at least column name containing the string ‘EMP.’
My SQL is ugly, so I’m not going to share it. I’ve shared the important bits already – the secret sauce to getting the object navigators to work, and I’ve reminded you that reports are frigging awesome, and that you can doc them wherever you want.
This is another good way to freak our your co-workers and remind THEM how frigging awesome YOU are 🙂
One last thing…
Reports are wonderful. The grids are filterable, searchable, exportable, all kinds of ‘able.’ You could also create child reports showing said columns for each table or even one called Views that showed you the views you wanted to see.
7 Comments
Hi! I just created a report like this and pinned it besides my connection browser, but when I restart SQL Developer and try to open the report, it instantly vanishes and I have to open it again from the reports tab and pin it again…
try running from the /bin directory, see if it catches an error in the console/cmd window
otherwise, send me your report so i can play with it…can’t make any promises
Thanks for your reply! This is the same behaviour with every report (you can try the default “sessions” report) – just pin the report window anywhere as a tab. Restart SQL-Developer and click on the tab-title to open the report and it will simply vanish immediately (probably because no connection is assigned to it?)
But this makes it very cumbersome, I have to re-open and re-attach custom report windows every day I restart SQL-Developer…
mmmm, so easy solution, don’t close SQLDev
in the meantime, I’ll see why we’re closing it
Just (re) discovered the SQLDEV:LINK: , after typing in my second drill down I created this package, greatly edited here for space.
create or replace package sqldev_helper AS
function dd_object( own varchar2, typ varchar2, name varchar2 ) return varchar2 deterministic ;
function dd_table( own varchar2, name varchar2 ) return varchar2 deterministic ;
function dd_index( own varchar2, name varchar2 ) return varchar2 deterministic ;
end;
/
sho err
create or replace PACKAGE BODY sqldev_helper AS
function dd_object( own varchar2, typ varchar2, name varchar2 ) return varchar2 deterministic
AS
BEGIN
RETURN 'SQLDEV:LINK:'
|| own
|| ':'
|| typ
|| ':'
|| name
|| ':oracle.dbtools.raptor.controls.grid.DefaultDrillLink';
END;
function dd_table( own varchar2, name varchar2 ) return varchar2 deterministic AS
BEGIN
RETURN dd_object( own,'TABLE', name ) ;
END;
function dd_index( own varchar2, name varchar2 ) return varchar2 deterministic AS
BEGIN
RETURN dd_object( own,'INDEX', name ) ;
END;
end;
/
sho err
now I can use
SELECT sqldev.dd_object( owner, object_type, object_name ) object
FROM my_tab ;
or the other helper functions if I know the type and TYPE is not in the table of interest.
Hi Jeff.
Nice feature! It reminds me the Toad for Oracle utility named Project Manager (can I write “Toad for Oracle” on this blog ? :D)
Cheers
A.
You just did 🙂