Search and Browse Database Objects with Oracle SQL Developer

thatjeffsmith SQL Developer 14 Comments

Tell Others About This Story:

I was tempted to throw in another Dora the Explorer Map reference here, but I came to my senses.

Having trouble finding something? Maybe you’re just getting older? I know I am. But still, it’d be nice if my favorite database tool could help me out a bit. Hmmm, what’s this ‘Find Database Object‘ thing over here…sounds like a search mechanism of some sort?

You can access this panel from the ‘View‘ menu. It’s a good bit down the screen, so I don’t blame you if you haven’t seen it before. It makes finding ‘stuff’ in your database so much easier. Let’s say I want to find my ‘beer’ objects.

I simply need to type my search string and the context (in this case I want it to search EVERYTHING), and hit enter.

The search results are listed below and clicking on an object automatically opens it!

I know it seems very simple, but I get asked this question a LOT.

It will even search through your PL/SQL code!

Finding too much? Be sure to toggle off the ‘%’ wildcard check box before doing a search.

Working on a Project?

I bet you use common column names, or codes, throughout your tables. You could take advantage of this knowledge and use the Find Database Object panel as a substitute connection tree or schema browser. Working on your HR project and want to look at your employee objects? Do a column search for your column ID/key.

Sometimes thinking outside the box actually works!

Don’t be afraid to tackle a problem from a weird angle, or re-purpose your tools. I do it all the time 🙂 And I drive the developers nuts trying to do things with the tools they were never designed to do. But I digress. Back to your coding!

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 14

  1. In the Find DB Object editor I could select several schemas and all schemas .
    With all I also see public objects. Is there a way to search for several schemas and public, but not for all?

    1. thatjeffsmith Post
      Author
      1. We use abbreviations for tablenames with public synonyms. Find DB Objects would be a quick help, if I can get rid of the clutter from other schemas.
        Therefor I would ask for such an enhancement in a future version 😉

  2. If you use (popup describe) to go to another package e.g. and you see this package in the editor. That’s nice.
    But can you lookup this newly opened package in the tree?
    Perhaps with a rightclick on the package source.
    We’re running version 4.1.2.20.

    1. thatjeffsmith Post
      Author

      you want to search every column and every table for a string?

      ummm, no

      think about how expensive that would be

      you COULD do it, you just have to write the ugly SQL to do it for you

      1. Yes i got the solution from oracle community

        https://community.oracle.com/message/11163181#11163181

        DECLARE
        ncount NUMBER;
        vwhere VARCHAR2(1000) := ”;
        vselect VARCHAR2(1000) := ‘ select count(1) from ‘;
        vsearchstr VARCHAR2(1000) := ‘[email protected]’;
        BEGIN
        FOR k IN (SELECT a.table_name
        ,a.column_name
        FROM user_tab_cols a
        WHERE a.data_type LIKE ‘%VARCHAR%’)
        LOOP
        vwhere := ‘ where ‘ || k.column_name || ‘ = :vsearchstr ‘;
        EXECUTE IMMEDIATE vselect || k.table_name || vwhere
        INTO ncount
        USING vsearchstr;
        IF (ncount > 0)
        THEN
        dbms_output.put_line(k.column_name || ‘ ‘ || k.table_name);
        END IF;
        END LOOP;
        END;

  3. Is there a way to export results ? I want the list of all the tables having a particular column, there are 100 results. I couldn’t find a way to export it nor copy paste it as text.

    1. thatjeffsmith Post
      Author
      1. Just right click on the table that is obtained in the output as a result of your query, there is an export option. Press that, and you will be asked for the format of your export file and the destination of that file.

  4. The “Find Database Object” utility just stopped working for me in that it won’t find columns or tables that I know are in connected database–I can simply use the table tree and find them. Is there some sort of search index function that needs to turned on to refresh the search index. It was working fine and then just stopped, I’ve already tried restarting SQL Developer. Thanks in advance!

    1. thatjeffsmith Post
      Author

      There was a bug where if you had ran a report, the Find DB Object editor would stop working. Have you ran a report? Does restarting the app fix the problem? I think this is fixed in 4.0.3, but I know for sure it’s fixed in v4.1

  5. “It will even search through your PL/SQL code!” – is relative.
    It finds declaration of variables in packages and other stored Pl/SQL modules using the content of “Name” field, but doesn’t fine declaration of functions and procedures in the same packages. That is really pity. It looks like the definition of term “Object” is not fully extended to cover procedures and functions declared within packages. Finding such “internal objects” is much more helpful as finding local variables in stored procedures.

    1. thatjeffsmith Post
      Author

      I knew that line was going to get me in trouble. And you’re right, we don’t search ALL_SOURCE, we instead look at views like ALL_ARGUMENTS. We’re going to fix that. And I’ll try to send you a note when it’s available to you.

Leave a Reply

Your email address will not be published. Required fields are marked *