Building An Object Search for SQLcl

存储架构 2018-03-14

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.

您可能感兴趣的

404错误是个啥? "404 错误"的前世今生 我是谁? 我从哪里来? 我要干什么? 这些问题已经很难再一一考究了。 人们都叫我“ 404 错误 ”, 我的本质一个 http网页错误代码 , 代表该网页不存在。 这些错误代码是 ...
编程语言不在于有没有钱途,在于你写的好不好... 抱歉,最近实在太忙,停更了一周多,当然忙肯定不是主要理由,是写文章的动力还不够,毕竟相比写文章,吃鸡游戏能吸引我更多,周末打了一天吃鸡,累死我了。 去了好久未逛的V2EX(基本每周看一次的节奏),有一种上错幼儿园大巴的感觉,探讨哪一种语言有“钱途”,这是个完...
Hibernate【缓存】知识要点 对象状态 Hibernate中对象的状态: 临时/瞬时状态 持久化状态 游离状态 学习Hibernate的对象状态是 为了更清晰地知道Hibernate的设计思想,以及是一级缓存的基础 ...当然啦,也就一点点知识 临时/瞬时状态 当我...
YugaByte’s new database software rakes in $16 mill... Looking to expand the footprint of its toolkit giving developers a unified database software that can work for both relational and post-relational dat...
Keycloak 3.4.1.CR1 发布,身份和访问管理 Keycloak 3.4.1.CR1 已发布。Keycloak 是一个针对现代应用程序和服务的开源身份和访问管理,为应用程序和安全服务添加最小化身份验证。无需处理存储用户或验证用户,开箱即用。 更新内容: Cross DC A lot of work has gone into...