Code Completion Insight with Oracle SQL Developer

Most third generation language (3GL) IDEs have trained hackers like me to expect the tool to write some of the code for them. Regular internet folks are already used to having search terms and URLs auto-completed in their browsers. I don’t want to get into a debate over whether this makes us lazier AND dumber, but I DO want to give you a quick overview of what SQL Developer can do in this area.

If you fall in the NOTEPAD – ‘do not put anything into the editor which I do not type myself’ camp, then I feel sorry for you. But also, rest assured that you can disable this feature in SQL Developer.

Turn it off, turn it off!

SQLDev can ‘think’ for you…

Love how it offers JOINS for me too!

…or SQLDev can ‘remember’ for you

auto-complete based on previous work

This is a REALLY nice touch and isn’t something I’ve seen in other database IDEs before. Let’s pretend that you have a database that is ‘missing’ foreign keys – crazy, I know. Instead of having to type out your joins manually, it’s nice that SQLDev can complete those for you now. So you type the join once and only once. The developer wins!

It’s all in the wrist

This is very subtle, and it took me a while to figure it out. What you see will depend on when you rest the keyboard and where. If I type ‘SELECT…WHERE’ and stop, I’ll see history items. If I type the same thing and add a SPACE after the WHERE, I’ll get the regular parsing and guessing what you want next items. If you can’t get the popup to ‘pop up’ when you want it to, you can always ask for it manually with the keyboard shortcut.

Hi Jeff
Just tested code completion in 17.2.0.188 Build 188.1159
It has unfortunately gotten worse. Now when I do the ‘Select * from pers’ [CTRL-Space] , I am getting packages and functions and even sequences, but no tables. I would guess that most people would just like to see the tables in their own schema when they hit CTRL-Space after writing ‘Select * from xxx’. I know that I don’t care about data in other schemas. And if I do need to see data in other schemas, then I log into that schema also. So, the solution is definitely not, “Just put the schema name first”. Perhaps there are some hidden parameters: ‘After Select … From, then show me tables’ and ‘Only show my schema objects’?
Going back to 4.1.5 for now 🙁

Hi Jeff
Just tested again. When I start a new worksheet (CTRL-Shift-N), then it is working as in 4.1.5. That is, it will show me the tables, but not the tables in my schema…only in other schemas. The popup list is so long that there isn’t enough room for the tables in my schema. This is why I think it should always show the connected schema’s objects first. 🙂
Now I know why code completion gives me Packages, Functions, Sequences. Something is confusing SQL Developer there. That is, if I have an invalid SQL query(with a ‘;’ at the end of the query) above the line that I am trying to do Code Completion on, then I get Packages, Functions, Sequences. So, if I have
‘select msn, actype
from acreg a, flightleg f
where a.msn = f.msn and;’
then code completion doesn’t quite work. This is not a big problem and something I can live with. It’s the ‘polution’ of table names from other schemas that is the real problem 🙂

Hi Jeff
You can see the screendump here:https://postimg.org/image/ykt6ivvc5/
—
And here is what I have from the log:
select ‘TABLE’ type, ”||o.owner# owner, name object_name, null column_name, null column_id, null data_type FROM sys.obj$ o
where o.name not like ‘BIN%’
and o.type# = 2
and rownum <=50
and name like ?
union all
select 'VIEW' type, ''||o.owner# owner, name object_name, null column_name, null column_id, null data_type FROM sys.obj$ o
where o.name not like 'BIN%'
and o.type# = 4
and rownum <=50
and name like ?
union all
select 'TABLE' type, owner, object_name, null column_name, null column_id, null data_type
from dba_objects
where object_name like ? and object_type = 'SYNONYM'
and rownum <=50
and owner in (SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ,'PUBLIC') 1="PERS%", 2="PERS%", 3="PERS%"

Yes, the connection name and schema name are the same. I get to see that schema’s objects if I scroll to the bottom of the list and double-click the ‘. . .’
This behavior doesn’t happen in all of my schemas.

Hi Jeff, I can give you a test case. You don’t see this behaviour because
1) You only have 2 tables that start with ‘pers’ and
2) You don’t have many schemas with tables that start with ‘pers’
Please have a look at this screenshot from schema grl201504:https://postimg.org/image/qnld5n90l/

So, the problem has to do with too many hits I think. There must be some ordering done after the select (the one from sys.obj$), because the select itself has no order by. When I run that select in SQL Developer, then the results come in owner# order, which is definitely not the order that I want them to be in. However they are ordered correctly when there are not that many tables that have to be returned. Most schemas I have ever worked with have well over 500 tables. How about adding a
select ‘TABLE’ type, ”||o.owner# owner, name object_name, null column_name, null column_id, null data_type
FROM sys.obj$ o
where o.name not like ‘BIN%’
and o.type# = 2
and rownum <=50
and name like 'PERS%'
and owner# in (SYS_CONTEXT('USERENV','SESSION_USERID'))
union all
at the top of the select that SQL Developer is running?

Thanks Jeff. Were you able to make any headway?
I find that the SQL formatting is different in 17.2. There is no ‘Number of Commas per Line’ option. Obviously this is another topic, but where should I post this?

It would be nice that when I type the name of a package and a dot, Insight could show the names of the public procedures/functions of the package.
For example:
pkg ABC has procedures ABC1, ABC2, & ABC3.

In the editor or the worksheet I will type ABC. (or ABC. + Ctrl+SPACE)
And a popup will show ABC1, ABC2 & ABC3.

If the procedures has parameters, it will simply add the stub of the parameters.
For example, after Insight a procedure ABC4 with 2 parameters (pParam1 varchar2(10), pParam2 varchar2(10)) will display:

Code completion works fine as long as you have an open connection to the data base. While coding there is no need to keep connections open. So, wouldn’t it be nice to have code completion cached? And just update when connected and (meta) data have changed.

Hi, Jeff!
may be i missed smth, but in one comment you had wrote: “Add schema prefix to your query and then invoke – only the local objects will come up, and when you select one, the schema prefix will be removed.”
and here is my 2 questions:
1. I need this prefix. How could I leave it?
2. When I use another schema prefix and then select table name from popup, schema prefix becomes UPPERcase, but I would like to see it in lowercase. Is there any solution for it?

Thanks guys.
Autocomplete behavior (matches from multiple schemas instead of current connection / not putting current connection on top of the list..) is a real pain. Looking forward for this bug fix ( +representing many of my colleagues)

Someone commented above:”. If I have typed enough of the table name, so that the list doesn’t have a scrollbar, then the current schema table is at the top of the list.” This seems to be the case in 4.0.3 and 4.1.1 . It’s actually very annoying. Tables from current schema should under every circumstance be listed on top. I have a couple identic schemas with tables named `leistung_…`Even if I have typed the full name of a table the list is still not short enough that it lists the table from current schema on top. You write “If you want to see less, type more.” – doesn’t work — unless I start with typing the schema: but why would I? I’m logged in as a certain user with a certain default schema.

Ok, so something weird is going on. Assuming you don’t have a login trigger which switches your schema context…I would log a bug. But be ready to share complete details to reproduce, or expect questions like you’ve seen here.

yep and they disable that option by default but i always used to enable it since it was very very handy – a lot of times you remember one of these keywords. Same in healthcare space – we have thousands of tables but normally tables have one of the keywords that many people know by memory.

Thank you for your article.
Can SQL Developer autocomplete by first letters? For example, I input the letter j and SQL Developer shows tables job and job_history.
At this moment i’m trying to input a first letter, but SQL Developer doesn’t want to autocomplete…

Btw any plans to use partial keywords? I really miss that feature from SQL Server SQL Assistant add-on. Basically if you type a partial keyword like order it would suggest all tables with “order” keyword regardless if order keyword is at the beginning, middle or end of the table name, so it will suggest:

While writing a query like suppose “select * from ” and as per the completion insight if I hit Ctrl+space it should show me the table names right, but Its not showing me anything even on repeated trails.

If it’s very very slow – have you considered your db might be far far away or it might be slow slow slow to respond to our queries we use to populate the insight lists?

We use ctrl-enter…because? That’s just what we use.

If you don’t like the keyboard shortcut, change it. It’s ‘completion insight’ in Tools – Preferences – Keyboard Shortcuts. Then map ‘insert tab’ to ctrl-enter and you’re good to go.

‘Queries are complicated’ – no idea. Give me a code sample. It’s very dependent on our parser. If there’s invalid sql in there, or you’re not using statement delimiters to separate them, it probably gets confused.

Give me examples using HR schema objects, and I can log a bug, if there is one.

We can’t improve it based on your experience if you never share your experience with us. So I really appreciate your feedback here and hope to get it right for you in our 4.1 patch we’re working on now.

thanks for your reply, Jeff. sorry if my message did not come across well – i am a big fan of your blog and we all really appreciate your team’s work improving SqlDev. In fact our other team was going to purchase TOAD and I begged them to try SQLDev explaining that it is very different from version 1 which everyone hated and that it works great now and became a really mature tool.

yes, I work with very very large and complex EHR database, thousands of tables and it is on remote server but on very fast network. I think we have over 50Tb of data if it matters..

Your question makes me wonder now – SQLDev does not cache the metadata and would query target database for every code insight request? that does not seem like a good idea – all third-party tools I’ve worked before actually would cache metadata upfront or in the background. I guess this could be a reason why it does not always work for me if it waits every time for request to get back.

I set code insight pop-up time to minimum 0.1 sec and a lot of times pop-up would not even show up so i have to press ctrl-space. I just changed it to 0.6 sec and pop-up now pops up more consistently. That was another annoyance for me to press ctrl-space almost always and i guess i just need to wait a bit more.

As for the query example, it is tough to replicate my queries to our EHR, since demo schemas are very simple. But I just tried the queries below on Oracle Dev Day VM and while most of the time code insight would work (after I set delay to 0.5 sec), it started to misbehave when i started to type where clause – just try to start typing t. and do it 10 times back and forth – in my case it would work 8 times out of 10. The other 2 times popup would not show-up even on ctrl-space. This is very simple query – normally i have to work with much complicated queries, with 15-20 joins, analytical functions, CTEs and subqueries. At some point code insight would just stop working. sometimes it helps to delete the last typed keyword and type it again and hit ctrl-space but sometimes nothing helps and i had to disconnect and connect to make code insight work again.

Is there a keyboard shortcut to select the first (or only…) item in the list when it pops up? Other (unnamed…ahem….) apps allow me to hit the tab or enter key to select the first item in the popup so I can just continue typing without having to go to the mouse to click to select…is really nice not to have to click or arrow down the list then enter…lazy? Maybe, but just one of those nice-to-haves…

i watch this gif several times and try, but why i cannot reproduce your scenario?i enter ‘ctrl+space’, it pops up, but then i enter ‘tab’,nothing happens?! this is exactly what we want,please solve my problem

I am seeing all of the schemas in the DB. And we have so many schemas that I don’t see the schema that I am currently connected to. Well I do, if I type 95% of the tablename(then the pop-up list is small enough). How do I get SQL Developer to only show those tables that are in my schema? I don’t care about the other schemas

Hi Jeff. Thanks for the quick response.
That’s correct: I type ‘select * from personnel’ and press ‘CTRL-Enter’. Then I get all tables that start with ‘personnel’ for every schema in the DB. The list is so long, that I don’t see all the schemas or even my own tables. If I type a bit more, ‘personnelce’, then the list gets narrowed down enough so that my own schema tables show up at the top. However, all other schemas are there as well.
Yes, I’m logged on as the schema owner and not as SYS. The user I’m logged on as has the DBA role.

That will work, but I don’t like that solution at all. I don’t remember it being like this in the 3.x version of SQL Developer. If you ask me, it’s a bug. When I’m in a schema, I don’t care about the tables in the other schemas. And the few times that I do, then I will prefix those. There must be a way to turn this ‘feature’ off.

I would also really appreciate an option to only show (or at least prioritize) the objects of the current schema in the code completion.

Most of our development users see all schemas of the dev. database, thus typing a table name without the current schema prefix yields a long list of the same object in other users schemas. (we use SQLDev Version 4.0.2.15)

Apart from this little annoyance, Completion Insight is a great feature. Thanks a lot.

So, there are other people who hate that other schemas are shown. If I want to find data in another schema, then I have an extra tab open in SQL Developer with that schema. What is the point of having code completion if I have to type over 90% of the word? I just don’t understand why this was introduced in 4.x. Jeff, you guys must be able to fix this. Pretty please with sugar on top.

Jeff wrote: “SELECT * FROM F – I see local schema objects that start with F, followed by other schemas’ objects that start with F. ”

This is exactly what i would expect to happen, but it doesn’t in my case.
When I type SELECT * FROM BLA_ i get only objects that are in other schemas starting with BLA_. There are too many results to suggest them all, so it shortens the list with a […] entry at the bottom of the list as expected. But non of the displayed matches are from my own schema.

If i type almost 90% of the table name and there are less than the maximum of results, it displays the object in my table first in the list and only then the remaining objects from other schemas. Just as expected.

It seems that this might be a bug in related to when there are more than the maximum of results that can be displayed in the list. It seems to prioritizes objects of other schemas when creating the subset of matches from the total.

I just tested it with SQLDev Version 4.0.3.16 (Build MAIN-16.84). Once with the imported properties from 4.0.2, and once with a fresh configuration.

The same problem with Code Completion Insight still occures.
As soon as there are more than 50 possible suggestions, the results no longer get sorted properly.

Instead of sorting all possible results by [objects of current schema > objects of other schemas > alphabetical order] it almost looks like SQLDev takes the first 50 suggestions of the unsorted result and only then sorts that subset alphabetically.
Unfortunately the chosen 50 suggestions are all from an other schema and not the current schema.

Bug or no bug, will it be ‘fixed’ in a coming release? My schema names are a lot longer than ‘hr’, so typing them is out of the question. I have 20 schemas that all have the same tables, so my list can be quite long.
Thanks, Thomas

Always so fast at responding you are! When I type
Select * from per
then I get all the schemas in alphabetical order that have a table starting with per. Many times, the table I want is so far down I have to scroll.
I will have to double-check on Monday(when I’m back in the office) if I am running the absolute latest version of SQL Developer.
If it always listed current schema tables first, then I would be happy. Or, if there was a checkbox in the Code Completion options, ‘Only show connected schemas objects’ (or something similar), then the user could choose to have them or not.
Thanks for listening.

I think Yannick has found the problem. It’s only when the list is too long that it puts other schemas first(at the top). If I have typed enough of the table name, so that the list doesn’t have a scrollbar, then the current schema table is at the top of the list.