Thursday, 19 September 2013

Utilizing SQL Developer autocomplete feature

Greetings, Audience.

Lets say, in your line of work (or maybe study) requires you to work with Oracle SQL, I would recommend that you use SQL Developer as the client IDE. It is quite lightweight, and currently maintained by Oracle itself.

Ok the point of this post is not to promote SQL Developer, but instead to show on how to utilize the autocomplete feature of SQL Developer. For this, I will show the example using a simple SELECT statement.

To begin, create the skeleton SELECT query from the desired table:
Be sure to follow the proper syntax so that the IDE won't complain (in which it might cause the auto-complete not to work). Use the indentation that best suit your style.

When you are typing the table name in the FROM clause, the auto-complete dropdown box will come out if you pause for several seconds. You can force this to come out by typing the first letter of the table name, and press CTRL + Space.

Next, build the WHERE clause. Link those tables and put restrictions. Here, the auto-complete box will come out when you type in the table name (or alias), followed by a dot '.'.
If somehow you accidentally do something that make the box disappear, you can force it to appear again by pressing CTRL + Space. Make sure that the typing caret is still nearby the 'dot'.

Once you've completed the WHERE portion, then only you come back to the SELECT part, and select the proper column that you want.
Similar to WHERE portion, the auto-complete will appear after any table and dots, and can be force to appear using CTRL + Space.

So.. Yeah.. That's pretty much about it. The main thing that you need to remember here is to always have the skeleton first. The auto-complete feature will appear based on the table that you choose.

Good luck, and have fun query-ing.
^.^

2 comments:

  1. I am using 19.1 version of SQL developer. But the auto complete for table names doesn't work here.

    ReplyDelete
  2. I'm on 19.2 and can't get is to work at all.

    Despite the shortcut to Completion Insight being set to Ctrl-Space,it has no effect.

    Also set the maximum matches suppression limit to 1000 and no auto-popup.

    ReplyDelete