ThatJeffSmith blog is about tips and tricks for Oracle SQL Developer, SQLcl, SQL Developer Data Modeler, and Oracle REST Data Services. My primary goal is to help you save time when working with Oracle Database. If I can ALSO make what you’re currently doing a little more fun, then that’s a big bonus for me.
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.
WHERE object_name LIKE :SEARCH
AND owner NOT IN (
AND object_type IN (
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.
Let’s say you have a schema you want to reverse engineer and generate some docs.
Lo and behold, the developers used foreign keys!
But…there are so many of them, I can’t see the forest for the trees.
This one table is touching almost everything…making everything else kind of hard to figure out.
So how do we make this simpler?
Well, our developer for the Data Modeler showed me this trick:
In his words:
when use synonyms is checked tables that are referred more than 9 times are removed from global picture (graphical synonyms are created) and layout in separate area on diagram thus those tables doesn’t introduce noise in the more meaningful relationships
So we separate the ‘trouble-maker’ table, and build a separate area of the diagram showing these relationships. That leaves the main area of the diagram free to show the rest of the model.
So we get this to show the VERY related object – ORDS_SCHEMAS – it has about 20 relationships, egads! – in its own segregated area of the diagram. Like so:
Already, so much better.
And then what about the rest of the diagram and objects?
Now we don’t let this one table overwhelm the others.
But I don’t want to see COMM_PCT AT ALL if it’s NULL, and I don’t want to ‘Fake It’ With a 0
Then we can write some pl/sql.
But it’s Sunday and I need to take the kids out for a bike ride, so I’ll get back to you on that.
OK, bike ride over. Now let’s look at ONE way to do this.
A stored procedure that returns 2 SYS_REFCURSORs.
CREATE OR REPLACE PROCEDURE getemployee_nulls (
emps OUT SYS_REFCURSOR,
empsnocomms OUT SYS_REFCURSOR
OPEN emps FOR SELECT *
WHERE commission_pct IS NOT NULL;
OPEN empsnocomms FOR SELECT employee_id,
WHERE commission_pct IS NULL;
Now, while this might be somewhat bespoke, I’m also going to take advantage of the AUTO feature we have for PL/SQL. While it’s not technically REST, being able to affect a RPC using HTTP and a POST without having to write any code is quite nice.
I see questions like this on StackOverflow and related websites like, ALL THE TIME:
I have some code, what’s wrong with it?
Something I wish I could do, other than physically hold the person’s hand, is show them how our IDE tries to point them in the right direction of their syntax issues. But since I can’t do that, I’ll write a quick blog post and help the Google gods can help folks going forward.
easbwaredgs_t AS k
LEFT OUTER JOIN easbdgstn_t AS p ON k.id = p.fk_easbwaredgsid
k.mandant = '001'
AND k.fk_easbware_id = 1
AND p.type_mc = 'TRANSPORT_DGS_LIM_QUANT'
) AS cc_is_limited_quantities
easbwaredgs_t.mandant = '001'
AND EASBWAREDGS_T.FK_EASBWARE_ID = 1
So, starting out from ‘scratch’, you might want some help with finding out where your problem is.
Sure, you could run the query and get the error line and cursor position, but generally our parser will identify problems AS YOU TYPE THEM.
It’s ‘all in the squiggle.’
The offending code is underline with a pink ‘squiggle’.
If you mouse over the underlined-text, we try to help you out with some doc links and expected ‘next’ words from the parser.
If you have a LOT of code, you don’t have to scan the editor looking for squiggles. You can pop open the Code outline. Any errors will be listed, and if you double-click on the items, they’ll take you to the offending line/curpos.
right-click in your worksheet or code editor and select ‘Code Outline’