The search feature in SQL Developer is whiz-bang.

You’re using it, right?

Look for stuff in your database – click the binoculars/search button on the main toolbar.

But what about at the command line?

I’m guessing many of you just pluck away at ALL_ or DBA_OBJECTS. Some of you may have written some custom scripts. But, what if you burned that into SQLcl?

You can of course do this with the ALIAS command.

You can say, ALIAS XZY=query;

And then access the query by just executing XZY.

AND, you can use positional binds!

So let’s take a look. I’m going to use this query.

SELECT owner,
  FROM all_objects
 WHERE object_name LIKE :SEARCH
   AND owner NOT IN (
   AND object_type IN (
    SELECT regexp_substr(:bind_ename_comma_sep_list,'[^,]+',1,level)
      FROM dual CONNECT BY
        regexp_substr(:bind_ename_comma_sep_list,'[^,]+',1,level) IS NOT NULL
 ORDER BY owner,

The only tricksy-part is the code around the object type list in the second predicate. I want to feed in a list of values to be used in a WHERE IN clause. Thankfully someone else already figured that out – thanks Arunkumar!

So, with that passed in, I can search for just tables and indexes with the text EMP in the name.

No spaces on the object_type list, and make sure everything’s UPPERCASE.

If you’re not lazy, you’re not a good developer…probably. And by ‘lazy’, I mean smart. I had to spend about 15 minutes here to save myself a few seconds every time I’m going to look for objects now.


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

Write A Comment

Follow by Email