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

That Jeff Smith稿源:That Jeff Smith (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Building An Object Search for SQLcl

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录