The search feature in SQL Developer is whiz-bang.
You’re using it, right?
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.
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.