Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Building An Object Search for SQLcl

2 Mins Read

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.

Author

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

Related Posts

2 Comments

  1. Delvin Reply

    Hi Jeff

    Is there an possibility of optionally using DBA_OBJECTS, if it is available, instead of ALL_OBJECTS in the SQL Develop search?

    My use case is that I have a login for the non-dev environments that can only access the source code of objects (package, package body) through DBA_OBJECTS but not ALL_OBJECTS. Hence, the object source code is not generated when I try to retrieve the it using the search functionality.

    Regards
    Delvin

    • thatjeffsmith Post Author

      If your user has access to the DBA_ views, we’ll use them when searching.

      For example, my DBA role user looks for source code containing text EMP –

      select NULL ENTRY, inner.* from (
      select text name, LINE, instr(upper(text),upper(:text)) COL, null USAGE, TYPE, OWNER, type OBJECT_TYPE, name OBJECT_NAME
      from sys.Dba_SOURCE where rownum <= 500 and upper(text) like upper(:text1) and name not like 'BIN$%' ) inner where rownum <= 500 order by case when OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') then ' ' else OWNER end, type, CASE WHEN USAGE = 'DECLARATION' OR USAGE = 'DEFINITION' THEN 0 ELSE 1 END, usage, object_name, line, col, name

      It's going against DBA_SOURCE, not ALL_SOURCE

Write A Comment