Building An Object Search for SQLcl

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,
       object_name,
       object_type
  FROM all_objects
 WHERE object_name LIKE :SEARCH
   AND owner NOT IN (
    'SYS',
    'MDSYS',
    'DBSNMP',
    'SYSTEM',
    'DVSYS',
    'APEX_050100',
    'PUBLIC',
    'ORDS_METADATA',
    'APEX_LISTENER'
)
   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,
          object_name,
          object_type;

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.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569