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:

Clicking on object in the report, opens said object.
Clicking on object in the report, opens said object.

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

7 Comments

  1. 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

  2. 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.

  3. SpaghettiOracle Reply

    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.

Write A Comment