You’re writing some SQL in Oracle SQL Developer, and you need help.
I mean really, who memorizes their data models?
Let’s go through the possible reasons this might not be working for you.
1. Your code is confusing the parser
The parser is the logic that we have that looks at your code and groks it. Oh, that’s a SQL statement, and that’s a table name.
If you scroll up above your statement that’s ‘not working’ – is there any invalid code? Did you remember to add a statement delimiter? If it’s funky, remove it, comment it out, or add a / or ; to gate it off from the code YOU’RE looking at.
You can see what the parser sees – open the Code Outline panel (on your editor right click menu.)
Same story about what’s below it – you don’t want the parser to think your SQL goes beyond what’s at the cursor.
2. Your object has too many columns
By default the ‘automatic’ insight feature filters the helper suggestions when there’s more than 10 ‘hits’ – because, how helpful would this be?
So how did I get this list?
HOT TIP: I used Ctrl+Spacebar to ‘force’ the insight suggestion.
If you want the automatic feature to be more forgiving, bump up the filter number appropriately –
3. Other weird things…
We can’t find anything. The parser gets it’s own connection to go tool around the data dictionary looking for ‘things.’ You can see this work if you open the View – Log (Statements) panel.
We asked, but didn’t get an answer fast enough. These queries can take time, and if your db is ‘huge,’ has outdated dictionary stats, or is ‘far away,’ the parser can decide to give up waiting. If you think this is happening, try hitting Ctrl+Spacebar a few times. I’ve seen this help.
In fact, ANY TIME you want to know how/what/why SQL Developer is doing what’s it’s doing, the Statements panel is like magic!
Making this better going forward
For version 19.1, we have a project to make this as fast and seamless as possible. For some folks even waiting a half second is apparently too much. So, stay tuned…
7 Comments
Hi Jeff, I’ve just moved from 21.4 to 22.2, and the above information (section 3) shows me why Completion Insight has broken for me.
The insight query has been changed to include “and owner in (‘PUBLIC’,’SYS’,user)”.
We use personal read only logins to access production for data mining, the logins do not own any of the objects – they are all owned by the relevant schema owners – so how do I remove this clause, or change the schema owners that are checked?
all_tables/ all_objects are already restricted to the objects that my user has access to, so I’m not seeing much value being added by the new addition?
I can confirm this – has completely gimped my experience, why would I want to look in public or sys?? I have rolled back to 20.4for now
because that’s where public synonyms are?
Awesome, thank you!
Jeff, I have kind of the opposite problem here that’s been annoying me for a while – code insight triggering inside comments. Even in a SQL window, if I type “–” I get the CREATE code insight popup. Any way to turn that off or disable it to only work with “active” code?
The automatic is on or off
I’ll test it within comments tomorrow and file a bug of necessary
Ugh, yeah I see that. I’ve logged a bug. Thanks for the report!