You’re writing some SQL in Oracle SQL Developer, and you need help.

I mean really, who memorizes their data models?

Why won’t this work!

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.)

An even then, the insight feature is still pretty robust.

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?

There’s THOUSANDS of suggestions to offer here – the automatic insight will NEVER offer you this list of suggestions.

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 –

Set it from 10 to 1000, or something.

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 looked, but couldn’t find anything.

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…

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.

7 Comments

  1. 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

  2. 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?

Write A Comment